Must I have ODBC table Linked for VBA code to work?

[Air_Cooled_Nut]

I have a small VBA module that pulls data from an ODBC data source and appends the data to a local Access table (from this thread: http://www.access-programmers.co.uk/forums/showthread.php?p=607605#post607605 ).

It works as long as I have a Linked Table from the ODBC data source. If I remove all Linked Tables coming from the ODBC data source, exit Access, then re-launch Access (now with no Linked Tables) and run my VBA code I get an error stating that

"[Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified."

But I [think] I have it in my code:

Set objConn = New ADODB.Connection: Set objCommand = New ADODB.Command: Set objRecordSet = New ADODB.Recordset
ConnString = "Driver={" & DriverName & "};Server=" & WTServer & ";Port=80;"
ConnString = ConnString & "Database=expressAnalysis;Uid=" & UserID & ";Pwd=" & Password & ";AccountId=" & AccountID
ConnString = ConnString & ";Language=english;ProfileGuid=xewTL6f9Fw5.wlp;SSL = 0;"
objConn.Open ConnString  'Open a connection to the data source

The error occurs at the last line you see in the code I've checked all the variables in the Locals window and they are all populated when I step thru the VBA code. And it does work when ANY table from the ODBC source is Linked to my db. Is there a way around this so I don't have to have a table from my ODBC data source Linked?

 

[vodafrog]

Hello

you do not state what type of datasource you are connecting to, MS SQL, Oracle, SAP, etc; I guess you are trying to create a DSN'less connection?

Assuming you are trying to connect to an MS SQL server, you could try adding the connection type into your string. This is achieved by adding the Network statement, see the following example: -

oConn.Open "Driver={SQL Server};" & _
"Server=xxx.xxx.xxx.xxx;" & _
"Address=xxx.xxx.xxx.xxx,1433;" & _
"Network=DBMSSOCN;" & _
"Database=myDatabaseName;" & _
"Uid=myUsername;" & _
"Pwd=myPassword"

Network=DBMSSOCN tells the DSN'less connection to use TCP/IP rather than Named Pipes, this could be the issue when you are not using an ODBC defined data source.

 

[Air_Cooled_Nut]

The documentation doesn't give much. It's for a WebTrends database and though it does use SQL it is a a little limited e.g. it doesn't support JOINs nor GROUP BY. From the text for the ODBC section:

The Driver then sends the query to the WebTrends UI Server using HTTP/HTTPS. The query is processed on the server and the result is sent back to the WebTrends ODBC Driver, which will pass the result to the client application through its ODBC API-compliant interface.

It further states ADO is the only way to access the data and the database is an ODDB, which means On Demand database.

 

[KeithG]

Have you tried a passthrough query? This is a query where you can use an ODBC driver to query an ODBC data source.

 

[Air_Cooled_Nut]

Have you tried a passthrough query? This is a query where you can use an ODBC driver to query an ODBC data source.

I haven't and I don't know how.

Just found out that WebTrends has a proprietary "db" that is essentially a collection of flat files What one 'pings' to get data from the WebTrends data source is just a bunch of files, not a real db Don't know if that helps any.

 

[vodafrog]

hello

on checking the Webtrends documentation the example connection string should be: -

Dim objConn As ADODB.Connection
Dim objCommand As ADODB.Command
Dim objRecordSet As ADODB.Recordset
Set objConn = New ADODB.Connection
Set objCommand = New ADODB.Command
Set objRecordSet = New ADODB.Recordset
objConn.Open "Driver={WebTrends ODBC Driver};Server=mywebtrendtsserver;AccountId=1;Database=edb_MyEventDatabase;Uid=
testuser;Pwd=tespassword;Port=7099;profileguid=3nXwrSka"

The example you provided appears to use a variable for the driver, server, username, password, etc. Are you sure these variables are being populated and also correct, especially the driver value as this has to be {WebTrends ODBC Driver} even if you have not setup a System DSN.

I assume you have also dimensioned your connection and recordset variables before you attempt to assign anything to them?

 

[Air_Cooled_Nut]

Yes, everything dimensioned and set and like I wrote above, everything is being populated as checked by the Locals window.

The variable DriverName was assigned the name of the ODBC connection. I just changed that value to the one you mentioned (and is in my documentation as well) to WebTrends ODBC Driver and everything worked fine. Interesting...though I don't understand why everything worked when a table was Linked.

Thanks for your help