 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / SQL Server / ADP / October 2003ADP access to a MDB tables
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 There's nothing stopping you opening the mdb file in code and creating recordsets etc from its tables. Regards Peter Russell > 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 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... > There's nothing stopping you opening the mdb file in code and creating > recordsets etc from its tables. [quoted text clipped - 8 lines] >> >>Matt 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 >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 [quoted text clipped - 13 lines] >>> >>>Matt 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! > 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. [quoted text clipped - 24 lines] >>>> >>>>Matt 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. > 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 [quoted text clipped - 29 lines] > >>>> > >>>>Matt 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 >I would import the table as is into the sQL then use that table to do the >view. [quoted text clipped - 35 lines] >> >>>> >> >>>>Matt 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. > 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 [quoted text clipped - 46 lines] > >> >>>> > >> >>>>Matt 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 >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 [quoted text clipped - 79 lines] >> >> >>>> >> >> >>>>Matt 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. > 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. [quoted text clipped - 87 lines] > >> >> >>>> > >> >> >>>>Matt 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 > True---- LOL > but they are importing 10 of thousands of records per day as different [quoted text clipped - 107 lines] > > >> >> >>>> > > >> >> >>>>Matt |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.