Go Back   Access World Forums > Microsoft Access Discussion > Tables

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 06-11-2018, 12:27 AM   #16
Gavx
Newly Registered User
 
Join Date: Mar 2014
Posts: 126
Thanks: 32
Thanked 0 Times in 0 Posts
Gavx is on a distinguished road
Re: updating linked table

Quote:
Originally Posted by Pat Hartman View Post
I frequently use procedures that process multiple files in one pass...
Thanks Pat, in fact one of your earlier posts set me on my current path.

Gavx is offline   Reply With Quote
Old 06-11-2018, 12:33 AM   #17
ridders
Newbee moderator
 
ridders's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 5,562
Thanks: 79
Thanked 1,379 Times in 1,286 Posts
ridders is a jewel in the rough ridders is a jewel in the rough ridders is a jewel in the rough ridders is a jewel in the rough
Re: updating linked table

Similarly, I have an automated routine that updates 30 csv files from an external source each night (using a scheduled task), processes them (as linked tables) & then imports the data into the main Access tables

None of the linked csv files are refreshed manually after being updated. It happens automatically

NOTE: In case of network issues, the existing csv files are saved as e.g. XXX.csv.old before they are overwritten
__________________
Colin
Access 2010 32-bit, Access 2016 32-bit & 64-bit, SQL Server Express 2014, Windows 10,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


If this answer has helped, please click the Thanks button and/or click the 'reputation scales' symbol on the left and leave a comment.

New example databases:
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


-------------------------
nil illegitimi carborundum est

Last edited by ridders; 06-11-2018 at 12:43 AM.
ridders is offline   Reply With Quote
Old 06-11-2018, 02:23 AM   #18
arnelgp
Registered User
 
arnelgp's Avatar
 
Join Date: May 2009
Location: somewhere out there
Posts: 6,196
Thanks: 54
Thanked 1,990 Times in 1,903 Posts
arnelgp is a jewel in the rough arnelgp is a jewel in the rough arnelgp is a jewel in the rough arnelgp is a jewel in the rough
Re: updating linked table

You dont need to do anything as long as the path is the same. Linked table only store the file and the path.

__________________
"Never stop learning, because life never stops teaching"
arnelgp is offline   Reply With Quote
Old 06-11-2018, 02:55 AM   #19
Gavx
Newly Registered User
 
Join Date: Mar 2014
Posts: 126
Thanks: 32
Thanked 0 Times in 0 Posts
Gavx is on a distinguished road
Re: updating linked table

Thanks everyone, unfortunately I am limited by the nature of the external source therefore can only process one file at a time, initiated by a manual process.
But that doesn't mean I can't make it more efficient, which I have as a result of this thread.
thanks again
Gavx is offline   Reply With Quote
Old 06-11-2018, 05:58 AM   #20
Gasman
Access newbie
 
Join Date: Sep 2011
Location: Swansea, South Wales,UK
Posts: 2,070
Thanks: 263
Thanked 309 Times in 294 Posts
Gasman will become famous soon enough Gasman will become famous soon enough
Re: updating linked table

I would consider perhaps changing your process.?
Download csv for bank and account, name it Bank_AccountNo.csv
Repeat for other banks and accounts.

Link to each csv file with the new names.(needed once only)

Process each in turn in Access. the formats might be different?
Delete/rename csv files so they do not get included next time.?


Sometimes you get the choice of naming during the download and so can overwrite the previous file.?



Quote:
Originally Posted by Gavx View Post
My workflow is to
  1. open database
  2. Return to internet and download csv data for particular account number
  3. Refresh linked table by opening and closing
  4. Perform queries on linked table
  5. Return to internet and download csv file but this time for another account number
  6. Open, refresh and close linked table
  7. Perform queries
  8. Loop etc

So the database remains open during the entire process.
How does liked table manager help?
Would the code be just opening and closing linked table or cannot be refreshed while it is closed...nah that sounds like a stupid question.
__________________
Access novice. Sometimes trying to give something back.
Access 2007
Gasman is offline   Reply With Quote
Old 06-11-2018, 01:38 PM   #21
Gavx
Newly Registered User
 
Join Date: Mar 2014
Posts: 126
Thanks: 32
Thanked 0 Times in 0 Posts
Gavx is on a distinguished road
Re: updating linked table

Thanks Gasman,
I cannot specify the name of the file unless I change it after the download.
The bank does not allow any interaction other than request the download.

I have eliminated the manual refreshing of the linked table (point 3 and 6).
Gavx is offline   Reply With Quote
Old 06-11-2018, 11:37 PM   #22
Gasman
Access newbie
 
Join Date: Sep 2011
Location: Swansea, South Wales,UK
Posts: 2,070
Thanks: 263
Thanked 309 Times in 294 Posts
Gasman will become famous soon enough Gasman will become famous soon enough
Re: updating linked table

Well I expect if you have eliminated 3 and 6, the process is a lot more streamlined and just a case of switching windows now?


Quote:
Originally Posted by Gavx View Post
Thanks Gasman,
I cannot specify the name of the file unless I change it after the download.
The bank does not allow any interaction other than request the download.

I have eliminated the manual refreshing of the linked table (point 3 and 6).

__________________
Access novice. Sometimes trying to give something back.
Access 2007
Gasman is offline   Reply With Quote
Old 07-11-2018, 08:36 PM   #23
Gavx
Newly Registered User
 
Join Date: Mar 2014
Posts: 126
Thanks: 32
Thanked 0 Times in 0 Posts
Gavx is on a distinguished road
Re: updating linked table

I have got this code to work before but now I can't, giving me a runtime error 3024, Could not find MyDB.accdb
Here is the code;
Code:
Private Sub cmdUpdate_Click()

Dim dbsCur As Database
Set dbsCur = OpenDatabase("MyDB.accdb")


Dim tdf As TableDef
    Set tdf = CurrentDb.TableDefs("tblCSVData")
    If Len(tdf.Connect) > 0 Then
        tdf.RefreshLink
    End If

End Sub
Gavx is offline   Reply With Quote
Old 07-11-2018, 08:47 PM   #24
Gavx
Newly Registered User
 
Join Date: Mar 2014
Posts: 126
Thanks: 32
Thanked 0 Times in 0 Posts
Gavx is on a distinguished road
Re: updating linked table

If I comment out
Code:
Dim dbsCur As Database
Set dbsCur = OpenDatabase("MyDB.accdb")

I am now getting the error I reported on post #8
Gavx is offline   Reply With Quote
Old 07-11-2018, 11:49 PM   #25
arnelgp
Registered User
 
arnelgp's Avatar
 
Join Date: May 2009
Location: somewhere out there
Posts: 6,196
Thanks: 54
Thanked 1,990 Times in 1,903 Posts
arnelgp is a jewel in the rough arnelgp is a jewel in the rough arnelgp is a jewel in the rough arnelgp is a jewel in the rough
Re: updating linked table

you need to put the complete Path.
__________________
"Never stop learning, because life never stops teaching"
arnelgp is offline   Reply With Quote
Old 07-12-2018, 02:46 AM   #26
Gavx
Newly Registered User
 
Join Date: Mar 2014
Posts: 126
Thanks: 32
Thanked 0 Times in 0 Posts
Gavx is on a distinguished road
Re: updating linked table

So I did as you suggested but still receive the same outcome
Here is the code;

Quote:
Private Sub cmdUpdate_Click()
Dim dbsCur As Database
Set dbsCur = OpenDatabase("C:\Users\Gavin\MyDB.accdb")


Dim tdf As TableDef
Set tdf = CurrentDb.TableDefs("tblCSVData")
If Len(tdf.Connect) > 0 Then
tdf.RefreshLink
End If

End Sub
Gavx is offline   Reply With Quote
Old 07-12-2018, 02:51 AM   #27
arnelgp
Registered User
 
arnelgp's Avatar
 
Join Date: May 2009
Location: somewhere out there
Posts: 6,196
Thanks: 54
Thanked 1,990 Times in 1,903 Posts
arnelgp is a jewel in the rough arnelgp is a jewel in the rough arnelgp is a jewel in the rough arnelgp is a jewel in the rough
Re: updating linked table

You already open the database but you did not use its instance. Which db does tblcsvdata located.
__________________
"Never stop learning, because life never stops teaching"
arnelgp is offline   Reply With Quote
Old 07-12-2018, 02:15 PM   #28
Gavx
Newly Registered User
 
Join Date: Mar 2014
Posts: 126
Thanks: 32
Thanked 0 Times in 0 Posts
Gavx is on a distinguished road
Re: updating linked table

tblCSVData is a table of MyDB.accdb

Gavx is offline   Reply With Quote
Reply

Tags
linked tables text files

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
[SOLVED] Updating a local table from a linked table workaround peskywinnets Modules & VBA 22 10-03-2016 01:47 PM
Updating Linked Table via Form-- HELP HELP MightyCat Tables 0 09-11-2012 01:41 PM
Linked table not updating??? gblack Modules & VBA 0 07-29-2010 01:30 PM
Updating from a Linked FoxPro table wlfarmer Modules & VBA 2 10-03-2000 02:29 PM




All times are GMT -8. The time now is 03:16 PM.


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

Sponsored Links

How to advertise

Media Kit


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