Hey Guys,

I have written an application in VBA using an ADODB connection to access tables in the current Access application. Now, because of security concerns, I have been asked merge the data to a SQL server 2000 database. How do I set up the connection string from my VBA application to SQL server 2000 so that my Visual Basic code will work with ADODB record sets?

Any input would be great.

Ryan


nikotromus

__________________
nikotromus

Hi nikotromus,

Here are standard SQL Server connection strings that use the SQL Server OLEDB provider:

Using SQL Server Authentication

Set cnn = New ADODB.Connection
cnn.Open "Provider=sqloledb;" & _
         "Data Source=yourServerName;" & _
         "Initial Catalog=yourDatabaseName;" & _
         "User Id=yourUsername;" & _
         "Password=yourPassword"


Using Windows Authentication

Set cnn = New ADODB.Connection
cnn.Open "Provider=sqloledb;" & _
         "Data Source=yourServerName;" & _
         "Initial Catalog=yourDatabaseName;" & _
         "Integrated Security=SSPI"

HTH,

Bob

Hi Bob,

Here is what I am trying. It is just a very simple example. I am using the connection string you gave me for sql server authentication.

  Dim cnn As ADODB.Connection
  Set cnn = New ADODB.Connection
  cnn.Open "Provider=sqloledb;" & _
  "Data Source=localhost;" & _
  "Initial Catalog=Customer;" & _
  "User Id=nikotromus;" & _
  "Password=******"

  Dim strqry As String
  Dim rsData As ADODB.Recordset
  Set rsData = New ADODB.Recordset
  rsData.ActiveConnection = CurrentProject.Connection
  rsData.CursorType = adOpenKeyset

  strqry = " select * from dbo_RyansTable "
  rsData.Open (strqry)

  Dim x As Integer

  x = rsData.RecordCount
  MsgBox x

This code is in a VBA form. The table dbo_RyansTable is in sql server 2000. What am I missing here? It does not recognize the table, so I am guessing that a connection has not been made.

Ryan



nikotromus

Hi nikotromus,

Once you create your connection object ("cnn") you need to use it, not CurrentProject.Connection. So replace:

rsData.ActiveConnection = CurrentProject.Connection

with

rsData.ActiveConnection = cnn

CurrentProject.Connection gives you a reference to your Access projects current ADO connection. So if you are using a .mdb file, CurrentProject.Connection will return something like:

Provider=Microsoft.Jet.OLEDB.4.0;
User ID=Admin;
Data Source=C:\yourDatabaseName.mdb;

Also, change the table name in your SELECT statement from "dbo_RyansTable" to "dbo.RyansTable" (assuming you named your SQL Server table "RyansTable", but not if you actually named it "dbo_RyansTable"). Or you can remove to SQL Server ownership prefix (dbo) completely, and just use "RyansTable". The OLEDB provider will handle the translation from JET SQL syntax to Transact-SQL syntax for you, adding the "dbo" prefix. If your Jet SQL statement contains a table named "dbo_RyansTable", the Transact-SQL statment run on SQL Server will look for a table named "dbo.dbo_RyansTable".

HTH,

Bob

Bob,

You kick ass! That was exatly it. It works perfectly now!

Thank you so much!

Ryan


nikotromus

LOL. Glad to help nikotromus :)

Gotta mention though, I need to retract my statement:

"The OLEDB provider will handle the translation from JET SQL syntax to Transact-SQL syntax for you, adding the "dbo" prefix."

Turns out that ain't what happens. I ran a SQL Server Profiler Trace, and this is what actually occurs when you submit your query to SQL Server.

Since you are using a server-side cursor (default), two remote procedure calls are made to two system stored procedures on SQL Server. The stored procedures create a cursor on the server to manage your recordset. Here is the SQL that gets executed (for a table named "RyansTable"):


exec sp_cursoropen @P1 output, N' select * from RyansTable ', @P2 output, @P3 output, @P4 output

exec sp_cursorfetch 180150001, 16, 1, 4

I you were using a client-side cursor (CursorLocation = adUseClient), SQL Server would simply execute a SQL batch command:

select * from RyansTable

and return the records to a cursor on your client machine, which is maintained by the ADO Client Cursor Engine (a COM component installed on your client machine).

The "dbo" prefix simply indicates that the object was created by someone logged on as a System Admininstrator, and is now owned by the "dbo" user account. It doesn't explicitly factor into the SQL statement.

Bob