We help IT Professionals succeed at work.

how to autoupdate an access database from a sql database

Medium Priority
413 Views
Last Modified: 2012-05-12
I have an access database that I built. I want to try and automate the data entry. I have a sql database that auto discovers new data. How can I automatically import at least part of the data from my SQL database into my access database.
Comment
Watch Question

CERTIFIED EXPERT
Top Expert 2016
Commented:
you can create a link (ODBC)  table from access to your sql database

see this link
http://www.ehow.com/how_4642566_setup-odbc-database-connection.html

Commented:
Starting point - http://www.codeproject.com/KB/asp/SQL_to_Access_Data_Export.aspx

That is a brief article for exporting data from SQL database to Access through a function.

It may include more than what you need, however it does provide code.

Alternatively, you could use a stored procedure / trigger combination.
Jeffrey CoachmanMIS Liason
CERTIFIED EXPERT
Most Valuable Expert 2012

Commented:
Can you explain the relationship between the SQL database and the Access database?

Author

Commented:
there is not a relationship right now. the sql database is a monitoring database that monitors my network. I have other data that I entered into the access database that my monitoring does not have. So I was hoping to import some of it from my sql databse into access
CERTIFIED EXPERT
Top Expert 2016

Commented:
does creating a linked table from the sql database not an option ?

Author

Commented:
that sounds ok to me. I'm trying to connect to it now. It's solarwinds and I'm not sure if it was setup with sql or sql express. I'm assuming the database is on the same server. I'm conected to the server via vpn and using a different login when I rdp to the server because it's in a different domain
Jeffrey CoachmanMIS Liason
CERTIFIED EXPERT
Most Valuable Expert 2012

Commented:
Then I would investigate the "Linking" technique the other Experts suggested.

If exact timing is an issue, then investigate a manual "Refresh" system.

Or adjust the settings that affect this: (see attached screenshot)

JeffCoachman

untitled.JPG

Author

Commented:
I created a linked table and I pulled data over. What I would really like for it to do would be to only import 2 columns into an already existing table appending data since some of the values will already be there. How  do I do that?
Jeffrey CoachmanMIS Liason
CERTIFIED EXPERT
Most Valuable Expert 2012

Commented:
This seems like a new issue, ...separate from your original issue.

If your original issue is now resolved, please accept the Post(s) that help solve this specific issue.
Then post this new concern as a new question.

;-)

JeffCoachman

Explore More ContentExplore courses, solutions, and other research materials related to this topic.