Question How to "requery" tables linked SQL Server (ODBC) (1 Viewer)

lake_tuna · Dec 27, 2010

I'm having trouble figuring out how to easily "update" or "requery" my tables (not forms) in Access 2010/2007 without having to re-import it every time I need updated tables. Note that "refresh" is not what I'm looking for.

I read extensively online and nothing works for me. I read a few articles on how to requery "forms" (not tables) and other articles with codes that don't do anything for my tables. Even MS's website's Refresh/Requery help section refers to requering "forms" and not tables. Does anyone have a macro or know an easy way to automatically or semi-automatically update the tables to have most recent data on them?

This the most promising turotial I found, but I'm unable to get it to work:

[It doesn't let me post links; perhaps I can PM you]

It doesn't do anything when I run it, and possibly the function call (
RefreshODBCLinks) parameters are not for my setup. I'm not sure what they are.

boblarson · Dec 27, 2010

I don't get why you need to "requery" or "update" your tables. If you need the current data to always be there you LINK to the tables and do not import them.

If you have imported them you would need to have to clear them and reimport them each time to refresh the data.

lake_tuna · Dec 27, 2010

boblarson said:
I don't get why you need to "requery" or "update" your tables. If you need the current data to always be there you LINK to the tables and do not import them.

If you have imported them you would need to have to clear them and reimport them each time to refresh the data.

The tables are already linked to the SQL database, and they show only the current data at the time of linking (sorry for the poor choice of work, import). They do not get updated as the SQL database gets modified. I will need to do this everyday, and it will be an annoyance to click and click and click through the same sequence every morning to get an updated set of tables. I'm trying to see if there's a macro that I can use to do it with a function call.

boblarson · Dec 27, 2010

lake_tuna said:
The tables are already linked to the SQL database, and they show only the current data at the time of linking (sorry for the poor choice of work, import). They do not get updated as the SQL database gets modified. I will need to do this everyday, and it will be an annoyance to click and click and click through the same sequence every morning to get an updated set of tables. I'm trying to see if there's a macro that I can use to do it with a function call.

Umm, you must have something else going on because linked tables ALWAYS show the most recent data. There is no Refresh or Requery necessary because when you open a linked database you ARE opening a new query.

lake_tuna · Dec 27, 2010

boblarson said:
Umm, you must have something else going on because linked tables ALWAYS show the most recent data. There is no Refresh or Requery necessary because when you open a linked database you ARE opening a new query.

Hey Bob, I think you just helped me figure out what was wrong! I was following someone else's guide on how to import/link (not sure which word was used in the guide), and after a full stomach I see that there are two options to display SQL data in Access, "import" and "link." I just created a new Access file with the link option and I'll check tomorrow when our SQL database gets updated!

Thanks!

boblarson · Dec 28, 2010

Yep, import actually imports the data from the tables and there is no link or dynamic interaction with the original tables. Link maintains a dynamic view of the data so you see what is there at any particular time.

ridwaniovin · May 13, 2012

i have similar problem. i created a ODBC link from the access database using the import option. but the problem is the access file is fetching data from the mysql database. but i want to send data from access to mysql.

how do i do that?

Pat Hartman · May 13, 2012

Welcome aboard

If you link to tables in MySQL or SQL Server or Oracle, etc., you can treat them the same way you would local tables (except that you can't change their design properties). Forms bound to queries of them will update the rows in the table on the server.

If you import tables from MySQL, etc., you are getting a static copy of the data at the point in time you did the import. You won't see changes made on the server and they won't see changes made in your copy.

ridwaniovin · May 16, 2012

when i create a link between the Access Database and Mysql, is there any way the table data in Mysql will be automatically updated from the Access database after exporting the first time?
My Access table is updated every hour. How can i synchronize the transfer of data between the two database?

Pat Hartman · May 17, 2012

Are you sure you are linking the table rather than importing it? Linked tables always show current data. If you link to the BE, the changes are immediately visible in both systems. If you are experiencing delays, it may be because you have forms open and there is a very long refresh time set.

When you open a form, Access runs the query specified in the form's RecordSource and brings that data into memory on the local computer. If you update records included in that recordset via some other FE, you won't see the changes until the form's recordsource is refreshed and you will NEVER see added records unless the RecordSource is requeried. Is this the problem you are experiencing?

tlockhart · Dec 28, 2015

I'm having a very similar issue, and have no idea how I can best handle this.

The original data sits on a MySQL database. I have read only access into this database. In order to accomplish what I need, I have to manipulate the data in Access. I have linked the tables via ODBC external data within Access 2016.

No matter what I do I cannot seem to get the data to re-query or auto refresh. If I leave Access open all day, and then return the following morning, the last record has not changed. As soon as I hit "refresh all", thousands of new records are now in the table. I'd like the database to automatically refresh any open table's I have.

I'm looking for these new records to automatically get added, once each evening, for each table I have open.

Any idea on what I'm doing wrong?

JHB · Dec 30, 2015

If you close the tables and open them, does the new data show then?

Question How to "requery" tables linked SQL Server (ODBC) (1 Viewer)

lake_tuna

New member

boblarson

Smeghead

lake_tuna

New member

boblarson

Smeghead

lake_tuna

New member

boblarson

Smeghead

ridwaniovin

New member

Pat Hartman

Super Moderator

ridwaniovin

New member

Pat Hartman

Super Moderator

tlockhart

New member

JHB

Have been here a while

Similar threads

Users who are viewing this thread