ODBC data sources in VBA using .ADP Access forms

[integrator1]

I am helping another programmer convert an application written using DAO and ODBC using a .mdb Access program connecting to multiple data sources to a .adp using Stored Procedures. One of the data servers is a SQL Server 7 database and the other is an ODBC compliant database running Pervasive SQL. THis application runs great under the .mdb using ODBC but is in need of redesign for its user base. I have selected to convert the program to a thinner client by placing the business rules on the backend using stored procedures. We have been successful in executing stored procedures to the SQL 7.0 database and populating the list boxes and such however, now the once-working ODBC connectivity to the Pervasive database server is giving us fits. It will not connect using DSN's and I cannot get SQL server to conect to it as a Linked Server. Does anyone have any code-pieces that connect a .adp Access program to an ODBC source? I have tried everything. All I want to do as a test is set up a simple button and textbox to bring back one field from the ODBC connection to my .adp form. This will verify connectivity and that we can see a field in the Pervasive database using ODBC. I'm assuming that this will require ADO programming by opening up the ODBC connection but am unsure. Please help.

Thanks in Advance.

 

[cmmrfrds]

"It will not connect using DSN's "

Are you doing this in the Access ADP?

Do you plan on joining the Pervasive tables with SQL Server tables on any SQL Statements?

Are you familiar with setting up an ADO connection string?

 

[integrator1]

cmmrfrds,
Thanks for your quick response. I may have jumbled two thought processes into my prior post. After attempting to connect to the ODBC database using DSN in an ADP, for some reason the Pervasive ODBC database can no longer be seen. It works just fine the old .mdb way using the same user permissions. The next step in my simple mind was to try to get the SQL Server 7.0 to connect to the Pervasive ODBC database using a Linked Server. The impetus here is to try see if the SQL7 engine had a better chance of making that connection work because of more robust libraries and such. No luck. SQL7 is complaining that the ODBC driver called Timberline ODBC (Timberline Accounting software alias Pervasive SQL) is not activated (whatever that means). Another strikeout. Now I've resolved myself to just making a simple ODBC connection using an ADP in Access to the Pervasive database and forgetting the SQL7 linked server idea for now. However, now we are stymied because we don't really know what is required to make this happen. I was hoping that some of the wizards on this forum could lead the blind through this narrow cavern as you have several times before In answer to your questions about joining Pervasive and SQL7 at some point, I think that is probably a possibility as soon as I can get this doggone thing connected. And, as for your last question, I am not, as well as the other programmer, familiar with setting up an ADO connection string. Can you help in this regard? I consider both myself and the other programmer, juniors, in ADO, but I have 18 years experience in C and other languages. I'm always learning.
Thanks in Advance for Your Continued Help,
integrator1

 

[cmmrfrds]

To start with here is a couple of examples of ADO connection strings.

SQL SERVER
'-- Reference Library
'-- Microsoft ActiveX data objects 2.6 library needed for ADO
Dim cn As New ADODB.Connection
Dim rs As ADODB.Recordset, connString As String
connString = "provider=SQLOLEDB.1;" & _
"User ID=sa;Initial Catalog=pubs;" & _
"Data Source=bigtuna;" & _
"Persist Security Info=False"
cn.ConnectionString = connString
cn.Open connString

EXCEL
connString = "provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\ATestDir\myTest.xls;" & _
"Extended Properties=""Excel 8.0;HDR=Yes;"";"
cn.ConnectionString = connString

ACCESS
connString = "provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=\\guppy\CWellsFargo\LSTMonthendReport.mdb;" & _
"Persist Security Info=False"

EASIEST WAY TO BUILD CONNECTION STRING is with the udl wizard. Try this and we can go from there.


Here is how you invoke the udl wizard. Do this on your desktop.

1. create a blank notepad file.
2. save the file.
3. rename the file by adding a new extention of .udl
make sure you save as all files not txt or some other
file type.
4. the icon for the file should change from notepad to a
little computer - it has now become the wizard.
5. double click on the file and it will bring up a dialog
box with multipule tabs.
6. use the microsoft access provider.
7. look under the ALL tab and there will be parameter settings which you can change if necessary. There is a test
button, press to test the connection.
8. close the file.
9. open the file from notepad instead of double clicking the icon. You will see the connection string which you can copy and paste into your program.

 

[cmmrfrds]

On ODBC from an Access project (adp) or SQL Server. Pervasive has an ODBC driver, but they also have an OLE-DB provider. When using ADO or Linked Server, I would avoid ODBC and use the OLEDB provider for Pervasive. If you use the udl wizard, you should see Pervasive as one of the provider options.

ODBC is for backward compatibility and is an extra layer of software drivers when using ADO or Linked Servers. ADO is part of Microsoft's MDAC library and has superceded ODBC for database connectivity. ADO is integrated with OLEDB.

An Access project (ADP) can only connect to one SQL Server database. So, you cannot link tables as is done in an Access MDB. There are other ways to accomplish using tables from other databases and servers. The most straight forward way is with linked servers, then all tables are available through the adp's active connection. You can also use the OPENROWSET (I am not sure about sql server 7 for this since I only have sql server 2000). Also, through VBA code you can connect to any table/server you want. It is more difficult through code to join across databases than using linked servers.

Hope this was some useful information to get you started.