Go Back   Access World Forums > Microsoft Access Discussion > General

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 12-27-2010, 02:05 PM   #1
lake_tuna
Newly Registered User
 
Join Date: Dec 2010
Posts: 3
Thanks: 0
Thanked 0 Times in 0 Posts
lake_tuna is on a distinguished road
How to "requery" tables linked SQL Server (ODBC)

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.

lake_tuna is offline   Reply With Quote
Old 12-27-2010, 02:22 PM   #2
boblarson
Smeghead
 
boblarson's Avatar
 
Join Date: Jan 2001
Location: Oregon, USA
Posts: 32,068
Thanks: 97
Thanked 1,828 Times in 1,579 Posts
boblarson is a splendid one to behold boblarson is a splendid one to behold boblarson is a splendid one to behold boblarson is a splendid one to behold boblarson is a splendid one to behold boblarson is a splendid one to behold boblarson is a splendid one to behold
Re: How to "requery" tables linked SQL Server (ODBC)

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.
__________________

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
boblarson is offline   Reply With Quote
Old 12-27-2010, 02:33 PM   #3
lake_tuna
Newly Registered User
 
Join Date: Dec 2010
Posts: 3
Thanks: 0
Thanked 0 Times in 0 Posts
lake_tuna is on a distinguished road
Re: How to "requery" tables linked SQL Server (ODBC)

Quote:
Originally Posted by boblarson View Post
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.

lake_tuna is offline   Reply With Quote
Old 12-27-2010, 02:35 PM   #4
boblarson
Smeghead
 
boblarson's Avatar
 
Join Date: Jan 2001
Location: Oregon, USA
Posts: 32,068
Thanks: 97
Thanked 1,828 Times in 1,579 Posts
boblarson is a splendid one to behold boblarson is a splendid one to behold boblarson is a splendid one to behold boblarson is a splendid one to behold boblarson is a splendid one to behold boblarson is a splendid one to behold boblarson is a splendid one to behold
Re: How to "requery" tables linked SQL Server (ODBC)

Quote:
Originally Posted by lake_tuna View Post
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.
__________________

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
boblarson is offline   Reply With Quote
Old 12-27-2010, 03:58 PM   #5
lake_tuna
Newly Registered User
 
Join Date: Dec 2010
Posts: 3
Thanks: 0
Thanked 0 Times in 0 Posts
lake_tuna is on a distinguished road
Re: How to "requery" tables linked SQL Server (ODBC)

Quote:
Originally Posted by boblarson View Post
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!
lake_tuna is offline   Reply With Quote
Old 12-27-2010, 04:13 PM   #6
boblarson
Smeghead
 
boblarson's Avatar
 
Join Date: Jan 2001
Location: Oregon, USA
Posts: 32,068
Thanks: 97
Thanked 1,828 Times in 1,579 Posts
boblarson is a splendid one to behold boblarson is a splendid one to behold boblarson is a splendid one to behold boblarson is a splendid one to behold boblarson is a splendid one to behold boblarson is a splendid one to behold boblarson is a splendid one to behold
Re: How to "requery" tables linked SQL Server (ODBC)

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.
__________________

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
boblarson is offline   Reply With Quote
Old 05-13-2012, 04:02 AM   #7
ridwaniovin
Newly Registered User
 
Join Date: May 2012
Posts: 2
Thanks: 0
Thanked 0 Times in 0 Posts
ridwaniovin is on a distinguished road
Re: How to "requery" tables linked SQL Server (ODBC)

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?

ridwaniovin is offline   Reply With Quote
Old 05-13-2012, 01:01 PM   #8
Pat Hartman
Super Moderator
 
Join Date: Feb 2002
Location: Stratford,Ct USA
Posts: 28,253
Thanks: 15
Thanked 1,592 Times in 1,512 Posts
Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all
Re: How to "requery" tables linked SQL Server (ODBC)

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.
__________________
Bridge Players Still Know All the Tricks
Pat Hartman is offline   Reply With Quote
Old 05-15-2012, 08:23 PM   #9
ridwaniovin
Newly Registered User
 
Join Date: May 2012
Posts: 2
Thanks: 0
Thanked 0 Times in 0 Posts
ridwaniovin is on a distinguished road
Re: How to "requery" tables linked SQL Server (ODBC)

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?
ridwaniovin is offline   Reply With Quote
Old 05-16-2012, 05:17 PM   #10
Pat Hartman
Super Moderator
 
Join Date: Feb 2002
Location: Stratford,Ct USA
Posts: 28,253
Thanks: 15
Thanked 1,592 Times in 1,512 Posts
Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all
Re: How to "requery" tables linked SQL Server (ODBC)

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?
__________________
Bridge Players Still Know All the Tricks
Pat Hartman is offline   Reply With Quote
Old 12-28-2015, 03:02 PM   #11
tlockhart
Newly Registered User
 
Join Date: Dec 2015
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
tlockhart is on a distinguished road
Re: How to "requery" tables linked SQL Server (ODBC)

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?

Last edited by tlockhart; 12-28-2015 at 03:11 PM.
tlockhart is offline   Reply With Quote
Old 12-29-2015, 11:42 PM   #12
JHB
Have been here a while
 
Join Date: Jun 2012
Location: In the south of Denmark (Jutland), near the German border.
Posts: 7,720
Thanks: 3
Thanked 2,082 Times in 2,037 Posts
JHB has a spectacular aura about JHB has a spectacular aura about
Re: How to "requery" tables linked SQL Server (ODBC)

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

__________________
If the above post has helped you, why not take the time to say thank you, by pressing the "Thumbs up."
JHB is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Programmatically omitting "Linked" Tables gblack Modules & VBA 9 04-16-2009 11:24 PM
Question Slow access 2003 search over ODBC SQL Server linked tables eligio General 9 11-21-2008 11:32 AM
How to convert the data shown when opening the SQL Server tables as "#" symbol accesser2003 General 0 08-13-2008 03:48 PM
[SOLVED] Linked Tables "Lost" bravon33 Tables 1 06-15-2006 07:39 AM
"ODBC Connection failed" in DoCMd.TransferDatabase "ODBC Database" Alika Modules & VBA 2 05-03-2004 07:37 AM




All times are GMT -8. The time now is 08:43 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World