Updating from an external Access table

  • Thread starter Thread starter Leif
  • Start date Start date
L

Leif

I need to be able to update two different Access tables
from an external Access database. I think I have a
solution, but it seems ackward.

Right now I creating a link to the external Access table
using docmd.Transferdatabase. Next I run a query that
does an unmatched selection joining the external and
internal tables. The external table is accessed thru a
different query that does some filtering.

Once I'm done with the query data I need to go back and
remove the table link.

Is there a better way? Pherhaps I can skip the
link/remove link steps by updating the filtering query
with the external database file name?

Regards,
Leif
 
there is no need to link, you can use IN Clause to refer to external mdb
SELECT CustomerID
FROM Customers
IN OtherDB.mdb
WHERE CustomerID Like "A*";

see online help for more info
 
Alex, thanks for your reply.

I know about the IN clause. I've used it before.
However, in this case I'm not interested in the results of
this query, instead it is being used in a second query as
a datasource to join with an internal table. Perhaps
there is a way to set the "Source Database" property of my
stored query, prior to it be referenced by my second query.

Regards,
Leif
 
Hi Leif,
not sure i understand everything. but you can change Source Database - just
change query SQL, modifying path to db for IN clause
 
Hi Alex,
Your thinking about a query created as part of a
recordset? I have a query already created. It is
referencing my link tabled. I think to use the IN clause
your thinking of a recordset created with a SQL statement
dynamically, is that correct? How then do I use that
query as a source for yet another query that does my
unmatch check?

What I do now works, it just seems ackward, since I create
a temporary link. It works as follows:

External Database
|
|
V
Table in current database (linked to above - dynamically
via VBA - docmd.TransferDatabase)
|
|
V
Query 1 (filters records from above - serves as source for
query 2)
| Table 1 (internal)
| |
V V
Query 2 (does an outer join on query 1 and table 1 in
current db to give unmached records to be appended to
table 1)

Final step - remove link to external table.
 
Hi Leif,

what i mean that you can have Query 1 reading a table in External Database
using IN. then you dont need to link table and remove it
 
Hi Alex,
OK, but the file (MDB) used as part of the IN is
requested at runtime through a dialog. How can I take a
stored (permanent) query and set the IN prior to executing
my second query (which will reference my first query).

Thanks,
Leif
 
Hi Leif,
i would make it like following:
1. make a query Query1Template with sql:
SELECT CustomerID
FROM Customers
IN [ExtDB]

2. after user enter a path to external db:
if len(dir(strExtMDB))>0 then
strSQL= dbs.querydefs(Query1Template").sql
strSQL=replace(strSQL, "[ExtMDB]",strExtMDB)
dbs.querydefs(Query1").sql=strSQL

'now run query2

end if

hope my idea is clear and can help you
 
Hi Alex,

Thanks for the code. However, there are a few problems.
Its interested, but if I try to put "[ExtMDB]" in the
query Access automagically removes the "[]" characters.
That was easily fixed by just using "ExtMDB" and changing
the replace to:

strSQL=replace(strSQL, "ExtMDB","'" & strExtMDB & "'")

However, the third statement:

dbs.querydefs("Query1").sql=strSQL

permanently changes the query. So, it runs the first
time, but not the second. So, at the end I need to reverse
out the change:

strSQL=replace(strSQL, "'" & strExtMDB & "'", "ExtMDB")
dbs.querydefs("Query1").sql=strSQL

Thanks,
Leif

-----Original Message-----
Hi Leif,
i would make it like following:
1. make a query Query1Template with sql:
SELECT CustomerID
FROM Customers
IN [ExtDB]

2. after user enter a path to external db:
if len(dir(strExtMDB))>0 then
strSQL= dbs.querydefs(Query1Template").sql
strSQL=replace(strSQL, "[ExtMDB]",strExtMDB)
dbs.querydefs(Query1").sql=strSQL

'now run query2

end if

hope my idea is clear and can help you

--
Alex Dybenko (MVP)
http://Alex.Dybenko.com
http://www.PointLtd.com


Hi Alex,
OK, but the file (MDB) used as part of the IN is
requested at runtime through a dialog. How can I take a
stored (permanent) query and set the IN prior to executing
my second query (which will reference my first query).

Thanks,
Leif in
External Database source
for property
of wrote
in have
a external
and
Click to expand...


.
Click to expand...
 
Hi Leif,
pls note that we read SQL from one query always:
strSQL= dbs.querydefs("Query1Template").sql

and write to other one:
dbs.querydefs("Query1").sql=strSQL

actually Query1Template used just to hold sql template, so this can be also
a string constant

--
Alex Dybenko (MVP)
http://Alex.Dybenko.com
http://www.PointLtd.com



Leif said:
Hi Alex,

Thanks for the code. However, there are a few problems.
Its interested, but if I try to put "[ExtMDB]" in the
query Access automagically removes the "[]" characters.
That was easily fixed by just using "ExtMDB" and changing
the replace to:

strSQL=replace(strSQL, "ExtMDB","'" & strExtMDB & "'")

However, the third statement:

dbs.querydefs("Query1").sql=strSQL

permanently changes the query. So, it runs the first
time, but not the second. So, at the end I need to reverse
out the change:

strSQL=replace(strSQL, "'" & strExtMDB & "'", "ExtMDB")
dbs.querydefs("Query1").sql=strSQL

Thanks,
Leif

-----Original Message-----
Hi Leif,
i would make it like following:
1. make a query Query1Template with sql:
SELECT CustomerID
FROM Customers
IN [ExtDB]

2. after user enter a path to external db:
if len(dir(strExtMDB))>0 then
strSQL= dbs.querydefs("Query1Template").sql
strSQL=replace(strSQL, "[ExtMDB]",strExtMDB)
dbs.querydefs(Query1").sql=strSQL

'now run query2

end if

hope my idea is clear and can help you

--
Alex Dybenko (MVP)
http://Alex.Dybenko.com
http://www.PointLtd.com


Hi Alex,
OK, but the file (MDB) used as part of the IN is
requested at runtime through a dialog. How can I take a
stored (permanent) query and set the IN prior to executing
my second query (which will reference my first query).

Thanks,
Leif
-----Original Message-----
Hi Leif,

what i mean that you can have Query 1 reading a table in
External Database
using IN. then you dont need to link table and remove it

--
Alex Dybenko (MVP)
http://Alex.Dybenko.com
http://www.PointLtd.com



message
Hi Alex,
Your thinking about a query created as part of a
recordset? I have a query already created. It is
referencing my link tabled. I think to use the IN
clause
your thinking of a recordset created with a SQL
statement
dynamically, is that correct? How then do I use that
query as a source for yet another query that does my
unmatch check?

What I do now works, it just seems ackward, since I
create
a temporary link. It works as follows:

External Database
|
|
V
Table in current database (linked to above - dynamically
via VBA - docmd.TransferDatabase)
|
|
V
Query 1 (filters records from above - serves as source
for
query 2)
| Table 1 (internal)
| |
V V
Query 2 (does an outer join on query 1 and table 1 in
current db to give unmached records to be appended to
table 1)

Final step - remove link to external table.

-----Original Message-----
Hi Leif,
not sure i understand everything. but you can change
Source Database - just
change query SQL, modifying path to db for IN clause

--
Alex Dybenko (MVP)
http://Alex.Dybenko.com
http://www.PointLtd.com


message
Alex, thanks for your reply.

I know about the IN clause. I've used it before.
However, in this case I'm not interested in the
results
of
this query, instead it is being used in a second
query
as
a datasource to join with an internal table. Perhaps
there is a way to set the "Source Database" property
of
my
stored query, prior to it be referenced by my second
query.

Regards,
Leif
-----Original Message-----
there is no need to link, you can use IN Clause to
refer
to external mdb
SELECT CustomerID
FROM Customers
IN OtherDB.mdb
WHERE CustomerID Like "A*";

see online help for more info


in
message
I need to be able to update two different Access
tables
from an external Access database. I think I have
a
solution, but it seems ackward.

Right now I creating a link to the external Access
table
using docmd.Transferdatabase. Next I run a query
that
does an unmatched selection joining the external
and
internal tables. The external table is accessed
thru a

different query that does some filtering.

Once I'm done with the query data I need to go
back
and
remove the table link.

Is there a better way? Pherhaps I can skip the
link/remove link steps by updating the filtering
query
with the external database file name?

Regards,
Leif


.



.



.
Click to expand...


.
Click to expand...
Click to expand...
 
Back
Top