ADP access to a MDB tables

[Matt Weaver]

I have an .ADP file which needs to access a table in a .MDB. When I try
to link to the MDB, Access tells me I can only like to tables when the
ADP connects to a local SQL server. TIA

Matt

 

[Peter Russell]

There's nothing stopping you opening the mdb file in code and creating
recordsets etc from its tables.

Regards

Peter Russell

 

[Matt Weaver]

I need to join on the table for a report (sorry, I should have been more
clear). I can't see how I would get the join to work through code, other
than importing records to a temporary table in SQL. I fully admit I may
not be sufficiently creative here...

 

[Mary Chipman]

You'd actually have better luck if you could use an MDB as a front-end
since it's really easy to link to two different data sources from Jet.
For an ADP, you'd need to create a linked server to the Access mdb,
then create views or stored procedures to query the linked server in
your SQLS database. Hard to think of another scenario which more
perfectly embodies the concept of kludge...

-- Mary
Microsoft Access Developer's Guide to SQL Server
http://www.amazon.com/exec/obidos/ASIN/0672319446

 

[Matt Weaver]

Ugh. Unfortunately, the project was already written as an ADP. Is there
a downgrade wizard ;-) ? As it is, I'm going to have an .MDB launched
when the report which needs to join to the table in another .MDB is
called. Thanks for everyone's help!

 

[BJ Freeman]

I would import the table as is into the sQL then use that table to do the
view.
then can be done in a macro or vBA. so you clear the table before importing
each time.

 

[Mary Chipman]

Importing the table to SQL Server at runtime is a ***REALLY BAD
IDEA***. First of all, you'd also need to script permissions since the
table would be owned by the current user, not dbo. Second, you'd have
the issue of cleanup, since everyone who runs the report will be
creating a new table. In addition, object creating imposes substantial
overhead on the server.

-- Mary
Microsoft Access Developer's Guide to SQL Server
http://www.amazon.com/exec/obidos/ASIN/0672319446

 

[BJ Freeman]

you are probably right, if the SQL sever does not have enough memory
assigned or CPU is like 150mhz, and the allocated space for the DB is not
large enough to handle that growth without doing an expand.

You say creating all these tables create overhead. How does that differ from
the creating a myriad of Tmp tables when doing queries, or Stored procedure.

You are right about permission. I come from a DB design schema where anyone
that connects to the DB, has to have SQL server userID. I keep forgetting
that a lot of people don't implement this. So there would be a table
conflict if everyone was using the Same SQL server UserID to logon.

Clean up is not an issue the way I proposed it, since the macro would have
the cleanup code in it.

I run several SQL servers in Real-time processing. My clients are always
exporting and importing data, on a daily basis. I don't see a major hit on
the CPU time or SQL performance as this is happening.
I have one SQL server that imports over 1.5 million records nightly. The CPU
coast along.

 

[Mary Chipman]

You may be importing 1.5 million records nightly in a batch process. I
bet you're not doing it on demand, whenever a user runs a report.
*That* is the issue.

-- Mary
Microsoft Access Developer's Guide to SQL Server
http://www.amazon.com/exec/obidos/ASIN/0672319446

 

[BJ Freeman]

True---- LOL
but they are importing 10 of thousands of records per day as different
tables.
and Yes they are on Demand, over the internet.

 

[Daran Johnson]

Creating tables at runtime creates additional overhead and is not a good
idea. Temp tables should also be avoided for that very reason. Other
issues are the use of indexes and stored procedure recomples. For stored
procedures there is a recomplie everytime a DDL statement is issued and if
you are interleaving your DML and DDL statements then your stored procedures
are constantly being recompiled. You must be very careful when using DDL
statements in an application. Whether or not your own applications run fine
right now is not the issue. It's sloppy design and drags down the
performanace of the entire server.

Now, as to the original question. Does the data in the Access file need to
be real-time? Can it be a day old or an hour old? If so, you can use DTS
and import the data into SQL Server and go from there. If not, then use
Mary's suggestion to link Access to SQL Server.

I hope that helps.

-Daran