Linking SQL Server Tables to Access via ADO

[AlexTeslin]

Hi,

I wrote a module which connects to Jet database (Access) through ADO and runs a query. The code runs only when i import those tables from a SQL server. When I try to link those tables (so that every time code runs a query generates updated results) I get "ODBC Connection to Server Failed" error. The connection code lookes like this:

cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\MyFolder\MyAccessFile.mdb;"

Set rst = New ADODB.Recordset
rst.CursorLocation = adUseClient
rst.Open "Resp_Create_Users", cn, adOpenKeyset, adLockPessimistic, _
adCmdTable

I am not sure whether I should try to link tables through access OR should I try to connect straight to Microsoft SQL Server? I heard that connecting directly to Server improves the performance, but I don't know by how much? If it does improves greatly, then perhaps I should consider the latter option? Also whatever is easier to implement?

Any suggestions will be much appreciated
Thanks

 

[LPurvis]

From where are you connecting to this Jet database? What application?
(Your connection opens a specific path, so where is this code running from in relation to the MDB which holds the tables - linked or otherwise?)

The linked table operates properly when opened from the Access window?

I have to admit - especially if you're doing this externally from that MDB file, if you're not then opening a connection to that running MDB file isn't the way to go - CurrentProject.Connection would be - I'd say connecting directly to the server makes more sense, unless you're planning on performing some heterogenous join in the end...
(Is there a reason you're opening the full table? You don't need only a subset of the data? Is there only a relatively small number of rows in the table anyway?)

FWIW (despite all my questions lol) your code should work, all things being equal.
Note, however, that you'll not be getting the type of recordset that you request - given that you're using a Client side cursor.

Cheers.

 

[AlexTeslin]

Thanks LPurvis,

I wrote my code in Visual Basic editor from Microsoft Access.
The tables are linked (and hence i get the error), but when are imported it works fine.
The linked tables operate fine when opened from the Access window.

I do not plan to do any heterogenous join, but the reason I am pulling queries in VB is to loop through records etc for complicated queries and to transfer records to Excel as well. So, I think for now I will stick to Access.

I have googled and found quite few sources, but the one was on microsoft site with some example code. It shows that I need to create through DSN. This is a link:
http://support.microsoft.com/kb/245587

Set rst = cn.Execute("SELECT * FROM [ODBC;DSN=MyServer;uid=myID;pwd=myPwd;database=Candidate_Meetings2].myTable WHERE FALSE")

I am not too sure if this code is what i need. Also, what is the database name, is it a .mdb file? I did run but no luck. Perhaps I am not using the right data, such as file names etc.

Thanks again

 

[LPurvis]

If this is executing from the local database (MDB) then rather than opening a connection (with potential concurrency issues) you'd use the current connection..
i.e.
Set cn = CurrentProject.Connection
rather than cn.Open ....

That wouldn't necessarily address an ODBC error - but if the table opens properly from the database window - that is the next thing to check.

As for the direct code connection - the ODBC connection method you've seen is one option - however I don't personally feel it's worthwhile when ADO has the OLEDB provider to connect more efficiently.
Your connection string could then look like

cnn.Open "Provider=SQLOLEDB;Data Source=ServerName;User ID=YourUN;Password=YourPW;Initial Catalog=DatabaseName;"

 

[AlexTeslin]

Thanks again for your reply,

I have changed to:

Set cn = CurrentProject.Connection

and it works fine.

Then I have included this code:
cn.Open "Provider=SQLOLEDB;Data Source=servername;User ID=myID;Password=myPassword;Initial Catalog=databaseName

I was getting run time errors complaining that I am opening already open object. So then i commented out the first line: ...CurrentProject.Connection.

When I run then, it takes few seconds, but then I am getting this error message: "[DBTENLIB][ConnectionOpen (Connect()).]SQL Server does not exist or access denied.

Can I also ask you what are servername and database name?

Is server name DSN name or is it something else?
And is databasename is the .dbm file, which i try to run in access?

Many thanks again

 

[AlexTeslin]

I probably need to mention that I do not need to be connected remotely to a server. I have already linked the tables to access file.

Also, we have different loginID and password to connect directly to the server and is limited to maximum 3 concurent users. But when I link tables from Access I need to provide different loginID and password. And i think this local server i need to connect to. Perhaps that is why i am getting the above error.

Thanks again

 

[LPurvis]

I'm not exactly following your requirements - but if you've got an active connection from the Access application and you have a connection limitation then you probably don't want to be connecting directly to the server.
Use the connection of the CurrentProject object to make use of the existing one.

I'm not following the different login and password. The same ones should be valid regardless of the technology you use to connect to the server. It's just a server login.

 

[AlexTeslin]

OK, I have an Access .mdb file. In it I have linked tables from a sql server, by going to File->Get External Data etc... Then I have created some queries based on these linked tables. When I open and run manually queries - they run fine - no problem. Then I have created modules as I need to filter, compare etc records and transfer to Excel workbook.
Now in my module I am connecting through CurrentProject, which only works if I Import those tables in Access (In other words copy them from a sql server). But this is obviously no good as I need live connection between those tables and sql server, so I can get updated data every time i run the code. But when I try to link the tables as mentioned above in Access, I am getting run time error.

In terms of different password: When I link those tables in Access from sql server, I was given different login and password. But I also have a permission to login to a sql server with another login and password. Perhaps I need to use only the one that is used when I link tables in Access?

 

[MagicMan]

If I have read this thread correctly,
1) You do not need to be attached to the Sql Server all the time...in fact if you are, you will limit the number of users
2) You need data from the SQL Server to do updates to your MDB

If these points are correct, create a pass through query to SQL Server. Open the connection, execute the pass through query, retieve your set of data from SQL Server, and disconnect.
Or...create a Stored Procedure on SQL Server, open a connect, dispatch the stored procedure, retieve the results and logoff the server.

 

[LPurvis]

There seems to be some fundamental difference between the actions you're taking.
You say the CurrentProject.Connection object only works for you if you've imported the tables?
How are you importing the tables. When you do so you must be providing some ODBC credentials - just as you do when linking... no?

Are you poviding different credentials at this time?

A passthrough is always an option from a reporting point of view (i.e. read only data) but you will need to provide those same valid credentials for the ODBC driver.

As far as an SP and opening a connection - a connection is exactly what we've been walking towards in this thread (with gradual instructions). Wether we then go down the route of an SP or an ad hoc request is almost irrelevant at this point until the connection issues are sorted.

Have you continued to try the ADO connection - with the same credentials you provide when you import the SQL Server data? (And, just to confirm, you do link tables with those same details??)

 

[AlexTeslin]

I am importing tables manually in Access by going to
File->Get External Data->Link Tables
This opens 'Link' window, then under 'Files of Type' selecting 'ODBC Databases()'
This opens 'Select Data Source' window, and under 'Machine Data Source' tab selecting 'myDSN' Data Source Name of type System.
This then opens 'SQL Server Login' box. Once entered login and password, the 'Link Tables' window opens where then I can select all the tables I need for my queries.

Then what I need to do is call the query from VB Editor and store in Recordset, do some filtering and transfer them to Excel.

The ultimate goal is that users can open Excel file and macro will run automatically the complicated queries without users touching any Access files.

What I was doing this morning was to record macro in Excel, then going to
Data->Import External Data->New Database Query
This opens the 'Choose Data Source' window, where I select SDN (same as in Access), then after login prompt opens 'Query Wizard - Choose Columns' wizard. After finishing the wizard, stopping the Macro and then I can view the code in VB Editor.
Now, I am thinking to use this code and work from here - not sure if will work though.

But I still think that connecting to sql server is the right and propper way.

Thank you

 

[MagicMan]

I understand now. Couple things...
1) Until the database is closed, you will be logged onto the SQL Server that you said has a limit of 3 users.
2) Im not sure about the importance of the user count on the Sql Server, but if it is important then..You may want to automate the process. Click an icon that lauches your DB, on the main form on load event, Link the tables using VB, you will be prompted for the User/password which you enter, then the VB code continues to create your Excel file, and closes the database. That way you are connected for the shortest possible time with no user interaction. (There are posts on these topics.)
3) You could do your data formulas prior to exporting to Excel in vba on Access.
Hope this helps
Bob

 

[AlexTeslin]

Thanks for a reply,

I might not make sense sometimes as I have very limited knowledge in SQL servers.

I think the maximum limit (3 in this case) is for connecting to a terminal server where Microsoft SQL Server resides and not to SQL Server itself. So I think I only need ID and Password for connecting to Microsoft SQL Server as when I manually link tables from SQL servver in Access.

Could you please provide more information about your 2nd point. Do you say that I need to change setting on SQL Server?

Thank you

 

[MagicMan]

It seems the connection to the server is not an issue. Point 2 was suggesting an automated process that establishes a very short term connection to the SQL Server. If the number of users is not an issue, then the automation is not required.

 

[AlexTeslin]

But why then I can't connect via ADODB.Connection, but can connect through Excel when I record Macro and Link tables from the SQL server manually?

 

[MagicMan]

These are methods for DSN-Less Connections to MDB or SQL Server

To Connect to Access MDB............................................................

For Standard Security:
oConn.Open "Driver={Microsoft Access Driver (*.mdb)};" & _ "Dbq=c:\somepath\mydb.mdb;"
If you are using a Workgroup (System database):
oConn.Open "Driver={Microsoft Access Driver (*.mdb)};" & _ "Dbq=c:\somepath\mydb.mdb;" & _ "SystemDB=c:\somepath\mydb.mdw;", _ "myUsername", "myPassword"
If want to open up the MDB exclusively
oConn.Open "Driver={Microsoft Access Driver (*.mdb)};" & _ "Dbq=c:\somepath\mydb.mdb;" & _ "Exclusive=1;"
If MDB is located on a Network Share
oConn.Open "Driver={Microsoft Access Driver (*.mdb)};" & _ "Dbq=\\myServer\myShare\myPath\myDb.mdb;"
If MDB is located on a remote machine
- Call an XML Web Service that contains data access web methods for MDB
- Or upgrade to SQL Server and use an IP connection string

If you don't know the path to the MDB (using ASP)
Make sure the Web identity has read/write permissions to the directory
the MDB is located in. e.g. "db" would need the read/write permissions.

If you don't know the path to the MDB (using VB)
oConn.Open "Driver={Microsoft Access Driver (*.mdb)};" & _ "Dbq=" & App.Path & "\myDb.mdb;" This assumes the MDB is in the same directory where the application is running.

To Connect to Sql Server ............................................................

Using the newer SQL Native Client driver (for SQL Server 2005)

For Standard Security
oConn.Open "ODBC;Driver={SQL Native Client};" & "Server=MyServerName;" & _ "Database=myDatabaseName;" & _ "Uid=myUsername;" & _ "Pwd=myPassword"
For Trusted Connection Security
oConn.Open "ODBC;Driver={SQL Native Client};" & _ "Server=MyServerName;" & _ "Database=myDatabaseName;" & _ "Trusted_Connection=yes"

Using the MDAC ODBC Driver

For Standard Security
oConn.Open "Driver={SQL Server};" & "Server=MyServerName;" & _ "Database=myDatabaseName;" & _ "Uid=myUsername;" & _ "Pwd=myPassword"
For Trusted Connection Security
oConn.Open "Driver={SQL Server};" & "Server=MyServerName;" & _ "Database=myDatabaseName;" & _ "Trusted_Connection=yes"
To connect to SQL Server running on a remote computer (via an IP address)
oConn.Open "Driver={SQL Server};" & _ "Server=xxx.xxx.xxx.xxx;" & _ "Address=xxx.xxx.xxx.xxx,1433;" & _ "Network=DBMSSOCN;" & _ "Database=myDatabaseName;" & _ "Uid=myUsername;" & _ "Pwd=myPassword"Where:
- xxx.xxx.xxx.xxx is an IP address
- 1433 is the default port number for SQL Server.
- "Network=DBMSSOCN" tells ODBC to use TCP/IP rather than Named
Pipes (Q238949)

Q238949 is Microsoft's ADO Connection Info.

As you can see, depending on your environment, there are many differing ways to connect. Not knowing your environment, this list should allow you to pick what you need. If you want a DSN connection then click the Q238949 link.