Access a DBase file from an ADP

[Steve101]

I have an Access ADP connecting to a SQL Server database. I need to also interface to a single DBase file from this ADP, which provides supplementary data, and can change regularly.

Can anyone suggest how I can get to this DBase data from within the ADP; so easy from an MDB using ODBC, but can I do it from an ADP?

Any help would be appreciated,

Cheers,

Steve Lewy
Solutions Developer
steve@lewycomputing.com.au
(dont cut corners or you'll go round in circles)

 

[SundancerKid]

Hi Steve,

You could use MS ACCESS standard (not ADP) and create links to your SQL and to data (mdb) that you wanted.

Regards,

SundancerKid A.K.A Rick

 

[Steve101]

Tnanks Sundancer,

As stated it HAS to be an ADP. Its an existing application deliberately written as an ADP top connect directly to SQL Server, but it now has an additional requirement to retrieve data from a DBF file.
I'd prefer a direct native connection to the dbf, but if all else fails I'll have to shell to an mdb to load the data to a sql table via odbc. Had hoped to avoid that though.



Steve Lewy
Solutions Developer
steve@lewycomputing.com.au
(dont cut corners or you'll go round in circles)

 

[Steve101]

gotcha!

Whilst its not possible to connect the dbase file to the tables connection, its possible to get to it through code using the SQL Server OPENROWSET capability; for example, the following SQL Server sql works great to join two DBase tables:

SELECT *
FROM OPENROWSET('MSDASQL','DRIVER={Microsoft Dbase driver (*.dbf)}',
                'SELECT * 
                 FROM C:\SomePath\Division.dbf') D
INNER JOIN OPENROWSET('MSDASQL','DRIVER={Microsoft Dbase driver (*.dbf)}',
                'SELECT * 
                 FROM C:\SomePath\Person.dbf') P
ON  P.ContactId = D.ContactId

So I'm guessing now that I can use this sort of construct to define ADODB recordsets or populate form recordsources / controls etc.

Same capabilities of course would apply to other data formats; eg Excel, text etc.

Steve Lewy
Solutions Developer
steve@lewycomputing.com.au
(dont cut corners or you'll go round in circles)