VBA & Linked Tables

[Guest]

Hello,

Can anyone tell me the Objects/methods I would need to access data through
linked tables that are in turn pulled from ODBC datasources?

MODEL
national database <-- ODBC <-- access Linked table <-- local access copy

of MICH data


Background
I have a couple database's that I connect to through ODBC. I pull copies of
my local (michigan) data down once a day using some queries.

I believe that with the correct VBA objects I can automate this task.
I also hope with VBA I can get more control over what I'm trying to do.


Thank you,

phill

 

[Dirk Goldgar]

Hello,

Can anyone tell me the Objects/methods I would need to access data
through linked tables that are in turn pulled from ODBC datasources?

MODEL
national database <-- ODBC <-- access Linked table <-- local
access copy

of MICH data


Background
I have a couple database's that I connect to through ODBC. I pull
copies of my local (michigan) data down once a day using some queries.

I believe that with the correct VBA objects I can automate this task.
I also hope with VBA I can get more control over what I'm trying to
do.

I think you may be making this more complicated than it should be. If
the table is linked to an ODBC data source, you don't need to do
anything special to get at the data, especially if all you want to do is
read it and copy it into a local table. I mean, if you have a linked
table "tblDataLinked", and you want to copy its contents into local
table "tblDataLocal" -- which is defined as having all the same fields
in the same order -- then all you need to do is execute an append query
like this:

CurrentDb.Execute _
"INSERT INTO tblDataLocal " & _
"SELECT * FROM tblDataLinked",
dbFailOnError

Maybe you need to apply criteria:

CurrentDb.Execute _
"INSERT INTO tblDataLocal " & _
"SELECT * FROM tblDataLinked " & _
"WHERE StateCode='MI'",
dbFailOnError

Beyond this, you'll need to explain what further issues are concerning
you.

 

[Guest]

Hello,

I think what you have said will work for me, but Just for continuity, how
would i access the tables in local.mdb from another mdb application?

I also use the tblLocalData in a Local.mdb file, then I access that
local.mdb from several different local applications that all need the
local.mdb data


example:

localApp1.mdb uses some data in local.mdb
tblApp1Data

localApp2.mdb uses some other data in local.mdb
tblApp2Data

local.mdb
tblLocalData

 

[Dirk Goldgar]

Hello,

I think what you have said will work for me, but Just for continuity,
how would i access the tables in local.mdb from another mdb
application?

I also use the tblLocalData in a Local.mdb file, then I access that
local.mdb from several different local applications that all need the
local.mdb data


example:

localApp1.mdb uses some data in local.mdb
tblApp1Data

localApp2.mdb uses some other data in local.mdb
tblApp2Data

local.mdb
tblLocalData

I'm not sure I understand, but you can have a linked table (or tables)
in each of your application databases, linked to tblLocalData in
Local.mdb. Once you've linked the table(s), you can treat them just
like any other table in localApp1.mdb or localApp2.mdb.