Making the switch to SQL Server

[stephenc]

I have written a utility to work with a shared database on our network. Below
is a cut-down sample from it which reads author names from a database table.
My problem is that the database has been moved to an SQL server and I don't
know how to modify my code to access the database there.

I've already set up an SQL Server Client on my PC, but can anyone point me
in the right direction on the VBA side, please?

Regards,

Stephenc

Dim Conn As ADODB.Connection, rs As ADODB.Recordset
Dim sConnectionString As String, sSQL As String
Dim myData() As String
Dim myRecordCount As Long

'Setting up the connection...
sConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & "X:\docdb\Docdb.mdb"
Set Conn = New ADODB.Connection
Set rs = New ADODB.Recordset

'Reading author names...
sSQL = "SELECT [ID], [Name], From [dbo_authors] ORDER BY [ID] ASC"
Conn.Open sConnectionString
rs.Open Source:=sSQL, ActiveConnection:=Conn, _
CursorType:=adOpenKeyset, LockType:=adLockOptimistic
rs.MoveFirst
lNumRecs = rs.RecordCount

'read all records into an array
ReDim Preserve myData(lNumRecs - 1)
Do While Not rs.EOF
myData(myRecordCount) = rs.Fields(1).Value
myRecordCount = myRecordCount + 1
rs.MoveNext
Loop
rs.Close
Conn.Close

'Set the objects to nothing
Set rs = Nothing
Set Conn = Nothing

 

[Rob]

I have a lot of templates that have code pretty similar to that. The only
real difference is the connection string, which looks like this:

sConStr = "driver={SQL Server};server=;database=;uid=;pwd="

Just put your server/db info in there and it should work.

 

[old man]

Hi,

You can also experiment with using a linked table (this way you can continue
using Access as a front end to SQL Server) until you are comforatable with
going directlly against SQL Server. Try File --> Get External Data in Access
and go from there...

old man

I have written a utility to work with a shared database on our network. Below
is a cut-down sample from it which reads author names from a database table.
My problem is that the database has been moved to an SQL server and I don't
know how to modify my code to access the database there.

I've already set up an SQL Server ,

Client on my PC, but can anyone point me

in the right direction on the VBA side, please?

Regards,

Stephenc

Dim Conn As ADODB.Connection, rs As ADODB.Recordset
Dim sConnectionString As String, sSQL As String
Dim myData() As String
Dim myRecordCount As Long

'Setting up the connection...
sConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & "X:\docdb\Docdb.mdb"
Set Conn = New ADODB.Connection
Set rs = New ADODB.Recordset

'Reading author names...
sSQL = "SELECT [ID], [Name], From [dbo_authors] ORDER BY [ID] ASC"
Conn.Open sConnectionString
rs.Open Source:=sSQL, ActiveConnection:=Conn, _
CursorType:=adOpenKeyset, LockType:=adLockOptimistic
rs.MoveFirst
lNumRecs = rs.RecordCount

'read all records into an array
ReDim Preserve myData(lNumRecs - 1)
Do While Not rs.EOF
myData(myRecordCount) = rs.Fields(1).Value
myRecordCount = myRecordCount + 1
rs.MoveNext
Loop
rs.Close
Conn.Close

'Set the objects to nothing
Set rs = Nothing
Set Conn = Nothing[/QUOTE]

 

[stephenc]

Thanks for the information (and to Old Man in this thread). It is exactly
what I needed

 

[stephenc]

Oh dear, I'm still having a problem with this. Below is my current connection
string:

Dim myServer As String, myDatabase As String
Dim myUID As String, myPassword As String

myServer = "Paris"
myDatabase = "Documents"
myUID = "admin"
myPassword = "admin"

sConnectionString = "driver={SQL Server};" & _
"server=" & myServer & ";database=" & myDatabase & _
";uid=" & myUID & ";pwd=" & myPassword

Set Conn = New ADODB.Connection
Set rs = New ADODB.Recordset
Conn.Open sConnectionString

On execution I'm getting runtime error "-2147217843 [Microsoft][ODBC SQL
Server Driver][SQL Server]Login failed for user 'admin'.

The good news is that I can make a connection to the database via the
relevant wizard in Excel 2007, so the ODBC setup in Data Courses (Control
Panel) is good.

I'd really appreciate some input on this please. Any ideas what I'm doing
wrong?