How can I write directly to a SQL 2005 table from MSAccess
I have six (6) make-table queries that extract data from a Deltek database via ODBC connection. I need to directly populate SQL tables with the data that now resides in my MSAccess db and I ain't gotta' clue . . .
I need a 'push' in the right direction.
Can I populate SQL tables from MSAccess? Can someone point me to online resources?
Ideally, the MSAccess db macro would 'kick-off' on a nightly schedule, grab the Deltek data, write to my MSAccess db, and then write to the SQL 2005 tables. We have a software package called PMPlan that will be using the SQL 2005 data.
| Deltek |>-------->|MSAccess|>----
tw
I need a 'push' in the right direction.
Can I populate SQL tables from MSAccess? Can someone point me to online resources?
Ideally, the MSAccess db macro would 'kick-off' on a nightly schedule, grab the Deltek data, write to my MSAccess db, and then write to the SQL 2005 tables. We have a software package called PMPlan that will be using the SQL 2005 data.
| Deltek |>-------->|MSAccess|>----
tw
GRayL
If you can 'link' to the SQL 2005 tables you should be able to use regular Access Append queries.
ASKER
I have no experience with SQl 2005. Where can I find info on how to 'link' to the SQL 2005 tables?
tw
tw
File, Get External Data, Link Tables, and first try linking directly. If that fails then after File, Get External Data, Link Tables, scroll down to the ODBC connection and select SQL 2005?
You can also pull data from an ODBC source (Deltek or MS Access) from SQL 2005 using SQL Server Integration Services.
This a more robust solution that using MS Access. I've found that MS Access Macros doesn't necessarily wait for one step of a macro to finish before going on to the next one.
There's a few more reasons to do it this way. However SSIS is a bit of step from MS Access so it may not be want you want.
This a more robust solution that using MS Access. I've found that MS Access Macros doesn't necessarily wait for one step of a macro to finish before going on to the next one.
There's a few more reasons to do it this way. However SSIS is a bit of step from MS Access so it may not be want you want.
ASKER
I am reading a document (http://msdn2.microsoft.com/en-us/library/bb188204.aspx), 'Optimizing Microsoft Office Access Applications Linked to SQL Server'. There is a section that discusses 'Working with Pass-Through Queries' as a means to populate the SQL 2005 tables. I already use one Pass-Through Query to grab Deltek data from a table that contains more than 255 columns. If I am understanding this correctly, I can take the tables that are now created in my MSAccess db and push the exact data, via Pass-Through Queries directly into the SQL 2005 tables?
Would this be a good solution?
tw
Would this be a good solution?
tw
No way you can import a table into Access with more than 255 fields. As such, the export via ODBC will not have more than 255 fields? Just my read on it.
I guess, why not Deltek directly to SQL 2005 ?
You are using MS Access as an ETL tool, which is not a very good idea. A more robust solution would be to pull the data into SQL Server 2005 without going through MS Access.
SQL 2005 has an ETL tool, a scheduler, all the stuff you need to do this.
SQL 2005 has an ETL tool, a scheduler, all the stuff you need to do this.
ELT - Extract, Transform, Load.
Google - ELT tool the fourth hit had some words about SQL 2005.
Google - ELT tool the fourth hit had some words about SQL 2005.
Actually ELT is extract-load-transform which is a slightly different method of doing the same thing. SQL 2005's ETL tool is called SSIS and you'll get heaps of hits for that.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
This question is still hanging around and I need to get some type of closure. Maybe if I rephrase the question a little.
Can MS Access be used as a 'Front-End' interface for SQL? In other words, is there a way to write directly to tables in a SQL db? I currently used Access to update attached SQL tables (update queries) but is this the only way?
tw
Can MS Access be used as a 'Front-End' interface for SQL? In other words, is there a way to write directly to tables in a SQL db? I currently used Access to update attached SQL tables (update queries) but is this the only way?
tw
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks. I will follow your suggestion and get back.
tw
tw
ASKER
This project kinda' died on the vine so I think it is best to close it out. Thanks for all of the help/comments.