We help IT Professionals succeed at work.

How can I write directly to a SQL 2005 table from MSAccess

269 Views
Last Modified: 2010-08-05
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|>-------->|SQL 2005|

tw
Comment
Watch Question

Commented:
If you can 'link' to the SQL 2005 tables you should be able to use regular Access Append queries.

Author

Commented:
I have no experience with SQl 2005. Where can I find info on how to 'link' to the SQL 2005 tables?

tw

Commented:
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.

Author

Commented:
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

Commented:
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.

Commented:
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.

Commented:
ELT - Extract, Transform, Load.

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.
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Author

Commented:
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
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Author

Commented:
Thanks.  I will follow your suggestion and get back.

tw

Author

Commented:
This project kinda' died on the vine so I think it is best to close it out. Thanks for all of the help/comments.

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.