ODBC Database Import / Link

[Allan]

Hi,

I'm currently working on new database project and data will be using ODBC
database. I would like to know which is more efficient (1) Importing or (2)
Linking?

Thanks.

Allan

 

[Clifford Bass]

Hi Allan,

It is not really a matter of efficiency. It has to do with what your
goal is. Which is not entirely clear. Are you saying that you will be
storing the live data out on a remote server and using ODBC to get to it? If
so, then if you import it into Access, you will have another copy and very
soon one copy or the other will become out-of-date. And you will need to
synchronize between the systems on a regular basis.

If that does not help, please be more specific.

Clifford Bass

 

[Allan]

Hi Clifford,

I will be using the data for viewing only and to generate reports and
queries so it is necessary to update the back end database regularly. With
this idea, i have decided to link from ODBC and now i'm encountering problem
as the linked database shows "#deleted" in all rows and column. I have posted
new queries. under the title "Table Linked to ODBC" and somebody advised me
to create unique id. i'm currently working on it. many thanks for the reply.

 

[Clifford Bass]

Hi Allan,

Based on what you say, your choice to link is the better one. Or a
pass-through query is another option, as mentioned in the other thread. It
does deal with changing data in the back end to some extent, but can make
creating queries based on it a bit of a problem since it has to go out every
time and execute the pass-through query in order to determine the field
information. At least that has been my experience.

That #Deleted thing could also be due to a bad ODBC driver, returning
invalid data somehow, and Access is detecting it. Technically a primary key
is not needed, but does go a long way to improving things. Access can
usually figure out the primary key--it queries the schema of the back end.
If it cannot, it will ask you to manually specify a "primary" key when you
link. This happens also when the back end "table" is really a view.

Good luck,

Clifford Bass