Pulling Data from SQL to Access DB

[JeremyAnderson]

Ok, so I am creating an Access Database to track the progress of our collections staff. I need to pull data from a remote SQL database and filter it so that the appropriate records are appended to the appropriate tables in teh Access DB. Any ideas?

 

[Pat Hartman]

Usually, there isn't any need to copy the data from SQL Server when you can just link to the tables from Access.

Start with File/Get External date/Link
Scroll to the bottom of the file type list.
Choose ODBC.
Choose SQL Server.
Look for the DSN for the database you want to link to. Add it if necessary.
Choose the tables you want to link from the tables list.

 

[JeremyAnderson]

Here is the reason why I need to make the copy. The data that comes from SQL db is live data. It changes constantly, I am wanting to copy it to the Access DB in order to have the previous days/weeks/months/years worth of data. The Access db will basicly be used to track trends and progress of our collections staff. I have been told this morning that it is possible via VB Script. Anyone know VB Script well enough to give me some example code that might help?

 

[Pat Hartman]

1. VB, VBA, and VB Script are separate but related languages. Access uses VBA NOT VB Script.
2. The instructions I gave you were for linking the tables. If you had followed them, you would have seen that you have a choice between link and import in the very first step.
3. By using queries that select only the data you need, you can use date ranges or other parameters to control the selected data.
4. How are you going to manage importing a complete set of data every day? What happens to the data after you report on it? By using the query method, you can run last week's report and get the same information as you got the first time you ran it without having to store a complete set of data every day.
5. You need to find a better advisor than the one who suggested VB Script.