Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access VBA
Password Reminder
Register
| FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
Access VBA Discuss using VBA for Access programming.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access VBA section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old February 13th, 2005, 06:11 PM
Authorized User
 
Join Date: Aug 2004
Location: rogers, ar, USA.
Posts: 34
Thanks: 0
Thanked 0 Times in 0 Posts
Default sql server 2000 connection string

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
Reply With Quote
  #2 (permalink)  
Old February 13th, 2005, 07:01 PM
Friend of Wrox
 
Join Date: Jun 2003
Location: , , USA.
Posts: 1,093
Thanks: 1
Thanked 12 Times in 11 Posts
Default

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


Reply With Quote
  #3 (permalink)  
Old February 13th, 2005, 07:19 PM
Authorized User
 
Join Date: Aug 2004
Location: rogers, ar, USA.
Posts: 34
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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
Reply With Quote
  #4 (permalink)  
Old February 13th, 2005, 08:43 PM
Friend of Wrox
 
Join Date: Jun 2003
Location: , , USA.
Posts: 1,093
Thanks: 1
Thanked 12 Times in 11 Posts
Default

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

Reply With Quote
  #5 (permalink)  
Old February 13th, 2005, 08:59 PM
Authorized User
 
Join Date: Aug 2004
Location: rogers, ar, USA.
Posts: 34
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Bob,

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

Thank you so much!

Ryan


nikotromus
Reply With Quote
  #6 (permalink)  
Old February 13th, 2005, 09:11 PM
Friend of Wrox
 
Join Date: Jun 2003
Location: , , USA.
Posts: 1,093
Thanks: 1
Thanked 12 Times in 11 Posts
Default

LOL. Glad to help nikotromus :)

Reply With Quote
  #7 (permalink)  
Old February 13th, 2005, 10:01 PM
Friend of Wrox
 
Join Date: Jun 2003
Location: , , USA.
Posts: 1,093
Thanks: 1
Thanked 12 Times in 11 Posts
Default

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






Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
AS400 connection to SQL Server 2000 dasoares SQL Server 2000 1 February 28th, 2007 10:41 PM
Sql Server 2000 Connection String ricpue BOOK: ASP.NET 2.0 Website Programming Problem Design Solution ISBN: 978-0-7645-8464-0 1 July 5th, 2006 05:26 PM
Problem with SQL SERVER Connection String Non Linear BOOK: Beginning ASP 3.0 3 October 20th, 2005 12:35 AM
SQL Server Connection String Nicky2k Classic ASP Basics 4 January 13th, 2005 05:22 AM
connection string for sql server rj1406 ASP.NET 1.0 and 1.1 Basics 6 September 16th, 2004 02:49 PM



All times are GMT -4. The time now is 04:26 PM.


Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.