updating linked table (1 Viewer)

Gavx · Jun 10, 2018

Using a web interface I export bank transactions into a csv file - the name of this file is always the same.
I have an access table that is linked to this csv file. I then query this table and do things.

Can the linked table be refreshed without opening it?

Or do I just run some code that opens and immediately closes the linked table?
Do I need to delay closing of the file in this scenario so that it has time to refresh?

thanks

isladogs · Jun 10, 2018

It will automatically refresh when you open your database or you can use the linked table manager for a no code solution. Otherwise use code.

Gavx · Jun 10, 2018

My workflow is to

open database
Return to internet and download csv data for particular account number
Refresh linked table by opening and closing
Perform queries on linked table
Return to internet and download csv file but this time for another account number
Open, refresh and close linked table
Perform queries
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.

isladogs · Jun 10, 2018

Gavx said:
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.

Selecting a table using the linked table manager & clicking OK refreshes the link. The table isn't opened doing so.
There were two questions there so I've ignored both!!!

However your workflow could indeed be shortened

To refresh all tables

Code:

Dim tdf As TableDef
For Each tdf In CurrentDb.TableDefs
  If Len(tdf.Connect)>0 Then
    tdf.RefreshLink
  End If
Next tdf

To refresh a named table

Code:

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

HTH

1268 · Jun 10, 2018

Each iteration you save a csv with the same name in the same spot?

Why do you need refresh? Maybe I am missing something.

Sent from my SM-G950U using Tapatalk

Gavx · Jun 10, 2018

1268 said:
Each iteration you save a csv with the same name in the same spot?

With each iteration a different run time parameter is specified... I can only create a csv for one account at a time, and I have multiple accounts.

isladogs · Jun 10, 2018

Ok - then see post 4

Gavx · Jun 10, 2018

ridders said:
...
To refresh a named table

Code:

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

The line

Code:

If Len(tdf.Connect)>0 Then

is throwing a "object invalid no longer set" error.
How should I fix this?

isladogs · Jun 10, 2018

Not at my computer now but I've used that code on numerous occasions without issues.

I'm sure you will have replaced Table1 with your own table.....
Check whether the code compiles. Perhaps a missing reference?

Gavx · Jun 10, 2018

Yes, replaced table1 with my name. Yes it compiles.

The enabled references are Visual Basic, Object Library, OLE Automation and the database engine object library.

isladogs · Jun 10, 2018

Not at my computer at the moment but can re-check later

Thinking further about this, are you sure it isn't updated automatically as 1268 suggested. The whole point about linked tables is that they are live

1268 · Jun 10, 2018

Gavx said:
With each iteration a different run time parameter is specified... I can only create a csv for one account at a time, and I have multiple accounts.

But the csv name, location and column header dont change do they? You just save over the old csv file?

Sent from my SM-G950U using Tapatalk

isladogs · Jun 10, 2018

@1268
x2!

Gavx · Jun 11, 2018

Well what do you know. Once I reopened the db it worked.

thanks for your help

Pat Hartman · Jun 11, 2018

I frequently use procedures that process multiple files in one pass. Assuming the files that get downloaded have unique names, just download all the files into the "input" folder. Write a code loop that links to each file in the folder, one at a time and appends the data to your permanent tables. As each file is finished, copy it to the "processed" folder and delete the file from the "input" folder.

Does that process sound like it would automate your procedure more fully?

Gavx · Jun 11, 2018

Pat Hartman said:
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.

isladogs · Jun 11, 2018

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

arnelgp · Jun 11, 2018

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

Gavx · Jun 11, 2018

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

Gasman · Jun 11, 2018

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

Gavx said:
My workflow is to

open database

Return to internet and download csv data for particular account number

Refresh linked table by opening and closing

Perform queries on linked table

Return to internet and download csv file but this time for another account number

Open, refresh and close linked table

Perform queries

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.

updating linked table (1 Viewer)

Registered User.

MVP / VIP

Registered User.

MVP / VIP

Registered User.

Registered User.

MVP / VIP

Registered User.

MVP / VIP

Registered User.

MVP / VIP

Registered User.

MVP / VIP

Registered User.

Super Moderator

Registered User.

MVP / VIP

..forever waiting... waiting for jellybean!

Registered User.

Enthusiastic Amateur

Similar threads

Users who are viewing this thread