• 0

Help: Access VBA data import from SQL


Question

Hi All

I?m looking for some help before i loose the plot.

Basically I am looking to make an external reporting tool in Access that in part uses a data table from our SQL database. What i want to be able to achieve by some means is a way to import this table from SQL to Access on a regular basis to ensure its up to date. Now i am aware i could link that table, but i wish to avoid this, probably making a rod for my own back but there you go.

Is there anyway by the means of a VBA script using ODBC, or such that could be executed from a form to initiate a refresh / replacement of a table from SQL to Access?

For reference

ODBC System DSN = MMSV3.5

SQL Server = sqlserver\mms

SQL DB = ReportsDB

SQL Table = dbo.BinItem

Any help would be greatly appreciated.

Cheers

Ross

Edited by Report Monkey
Link to comment
Share on other sites

3 answers to this question

Recommended Posts

  • 0

You can use a ADO connection to the database and get the data that way in VBA.

You can have a table structure in access that mimics the table you wish to "link" to, then in VBA:

1)Delete all entries in the access table

2)Make an ADO connection to your database, http://www.connectionstrings.com/ for connection strings

3)Copy the data you want into your access table

4)Close the connection.

Just an idea.

Link to comment
Share on other sites

  • 0

Thanks for that, sounds like that should do the job. I hate to be a pain, could you point me in the direction of some code examples as I?m not very proficient with VB.

Link to comment
Share on other sites

  • 0

The way I gave above would be very slow with a lot of records, and if you are not that proficient in VB, then maybe you can do it another way?

I would try a pass-through query and just copy and paste the results in a local table, or create an append query to load the data into the local table.

How to set up a pass-through query in access - http://support.microsoft.com/kb/303968

Link to comment
Share on other sites

This topic is now closed to further replies.
  • Recently Browsing   0 members

    • No registered users viewing this page.