Linked Tables

[Sharp]

Hi

I am working with a dataset that is updated frequently.
Therefore, I have decided to create linked tables instead of importing them.
That way when the dataset is updated, I just replace the files and the
tables will be automatically updated.

PROBLEMS:

1.
The linked tables cannot have a primary key. In fact, you are not permitted
to change anything.
I assume if a field that should be a primary key is unique then things will
work correctly anyway.
The primary key is used just for indexing purposes so that queries work
faster?

2.
Extremely slow for queries.
Nothing you can do I guess.

Cheers
Sharp

 

[Rick Brandt]

Hi

I am working with a dataset that is updated frequently.
Therefore, I have decided to create linked tables instead of
importing them. That way when the dataset is updated, I just replace
the files and the tables will be automatically updated.

What do you mean "replace the files"? When you update data in a link the data
on the source is updated in real time. There is no "replace the files" step
involved.

PROBLEMS:

1.
The linked tables cannot have a primary key. In fact, you are not
permitted to change anything.

Are you saying that your particular tables don't have PKs or are you under the
impression that linked tables NEVER have PKs? Because the latter is definitely
incorrect. If the tables have a PK defined at the source then Access will use
them. Are these going to be linked Jet tables or from an ODBC source (SQL
Server for example).

I assume if a field that should be a primary key is unique then
things will work correctly anyway.

If you link to an ODBC source and the table has no PK and no Unique Index at the
source then Access will ask you to specify which columns it can use to build a
local index with and then the link will be editable.

The primary key is used just for indexing purposes so that queries
work faster?

All indexes help query performance (when they can be used), but that is really a
different subject than the reason to have PKs.

2.
Extremely slow for queries.
Nothing you can do I guess.

I'm not sure what you mean here either. Queries against links are not
inherantly slower. It just depends on setting things up correctly. And if the
back end is a server database then you can use pass-through queries if local
ones have performance problems.

 

[Sharp]

What do you mean "replace the files"? When you update data in a link the data
on the source is updated in real time. There is no "replace the files" step
involved.

I'm creating linked tables from text files that I download from a website.
The website frequenty updates these text files.
So in this case, there is a need to replace the out-dated files.

Are you saying that your particular tables don't have PKs or are you under the
impression that linked tables NEVER have PKs? Because the latter is definitely
incorrect. If the tables have a PK defined at the source then Access will use
them. Are these going to be linked Jet tables or from an ODBC source (SQL
Server for example).

You cannot assign PKs in text files.
That said, what format allows you to assign PKs.
Again, this would require an additional step, making importing tables the
better option.

I'm not sure what you mean here either. Queries against links are not
inherantly slower. It just depends on setting things up correctly. And if the
back end is a server database then you can use pass-through queries if local
ones have performance problems.

Have you tried queries using linked tables?
They are obviously slower because Access has to "load" the data from the
file for every query.
For multiple joins, this is a very slow process.

Cheers
Sharp

 

[Rick Brandt]

I'm creating linked tables from text files that I download from a
website. The website frequenty updates these text files.
So in this case, there is a need to replace the out-dated files.

Everything you are describing is due to the fact that you are using text files,
not because they are linked. The fact that you failed to include this critical
piece of information in your original post basically makes my earlier response
completely irrelevant.

 

[Sharp]

Everything you are describing is due to the fact that you are using text files,
not because they are linked. The fact that you failed to include this critical
piece of information in your original post basically makes my earlier response
completely irrelevant.

My sincere apology.

What format allows you to assign PKs?
I want to be able to link to a dataformat where I can assign a PK.
Tried textfiles and excel spreedsheet, and doesn't work.

Cheers
Sharp

 

[Rick Brandt]

My sincere apology.

What format allows you to assign PKs?
I want to be able to link to a dataformat where I can assign a PK.
Tried textfiles and excel spreedsheet, and doesn't work.

Cheers
Sharp

I don't think a link to a text file is ever updateable. This has nothing to do
with PKs but is just the nature of the driver used to create the link. You'll
likely need to stick with your previous method of importing the data.

 

[Sharp]

I don't think a link to a text file is ever updateable. This has nothing to do
with PKs but is just the nature of the driver used to create the link. You'll
likely need to stick with your previous method of importing the data.

It has updated fine for me so far.
Do you want to elaborate on the driver problem?

Cheers
Sharp

 

[Rick Brandt]

It has updated fine for me so far.
Do you want to elaborate on the driver problem?

I could be wrong, but I just tested linking to a text file again and if I
try to edit the data in the link I get the message...

Updating data in a linked table not supported by this ISAM.

 

[Sharp]

I could be wrong, but I just tested linking to a text file again and if I
try to edit the data in the link I get the message...

Updating data in a linked table not supported by this ISAM.

Nevermind, I think you are confused by what I mean by updating the out-dated
data.
Maybe you want to read over what I had explained earlier.

Cheers
Sharp

 

[Amanda Payton]

Sharp -

I understand what you're talking about...

I have a couple of databases that are updated at regular intervals (daily or
monthly) from CSV files (comma separated values) which is one particular
text file format.

There are definite pros and cons to linking to data sources other than
database tables.

The pros are that you don't have to have code or macros to transfer data
into the database. When you change the external source, your data
automatically changes.

The con is that Access has to process that file every time you want
information out of it (slow queries)... and if you ever DO make an update or
append query on that external source - Access gets VERY cranky.

If speed is of the essence (as it is in my case), I reccommend something
similar to what I've used.

Set up a single field, single record table to store a date and time. This
will be the last time your program updated itself from the external data. (if
you have any tables that store mundane information, like filepaths, you might
tack this on to that as a new column

Set up a sequence of code to run whenever you open up your database. (I
created a 1 line autoexec macro that calls my code function "Startup()")

Use the FileDateTime("Source") function to retrieve the date and time of the
last creation or modification made to your external files. If the files will
ALWAYS be updated as a group - then you might need to check only one file
date - otherwise... build a loop to look at all your files with
FileDateTime() and find the most recent update time. Compare this to the
date and time you stored (I use DLookup() to retrieve it). If the external
files are newer than the last time you updated - then execute a sequence of
events to pull the external data into tables inside your database.

THEN - all your queries and data retrieval can reference the tables IN your
database( which you can create with primary keys) and life is much speedier...

HTH... if you've never coded before - say so... maybe we can come up with
something here...

-Amanda

 

[Sharp]

Sharp -

I understand what you're talking about...

I have a couple of databases that are updated at regular intervals (daily or
monthly) from CSV files (comma separated values) which is one particular
text file format.

There are definite pros and cons to linking to data sources other than
database tables.

The pros are that you don't have to have code or macros to transfer data
into the database. When you change the external source, your data
automatically changes.

The con is that Access has to process that file every time you want
information out of it (slow queries)... and if you ever DO make an update or
append query on that external source - Access gets VERY cranky.

If speed is of the essence (as it is in my case), I reccommend something
similar to what I've used.

Set up a single field, single record table to store a date and time. This
will be the last time your program updated itself from the external data. (if
you have any tables that store mundane information, like filepaths, you might
tack this on to that as a new column

Set up a sequence of code to run whenever you open up your database. (I
created a 1 line autoexec macro that calls my code function "Startup()")

Use the FileDateTime("Source") function to retrieve the date and time of the
last creation or modification made to your external files. If the files will
ALWAYS be updated as a group - then you might need to check only one file
date - otherwise... build a loop to look at all your files with
FileDateTime() and find the most recent update time. Compare this to the
date and time you stored (I use DLookup() to retrieve it). If the external
files are newer than the last time you updated - then execute a sequence of
events to pull the external data into tables inside your database.

THEN - all your queries and data retrieval can reference the tables IN your
database( which you can create with primary keys) and life is much speedier...

HTH... if you've never coded before - say so... maybe we can come up with
something here...

-Amanda

I have never coded before on Access.
Can you teach me?
Perhaps you have a personal e-mail address I can contact you on.

Cheers
Sharp

 

[Amanda Payton]

Sharp -
(sorry about the delay - have been away from my computer for a few days)
I don't mind helping.... but I don't post my personal e-mail address....

Have you ever programmed before in ANY language?

I highly reccommend picking up a book from Borders or Barnes & Noble etc.
on VBA. That will give you far more info than I could give you here.

If you want to post your address - (or some form of it, I'll respond there...)

Let me know how you'd like to proceed

-A