By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
464,312 Members | 1,161 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 464,312 IT Pros & Developers. It's quick & easy.

collect data from exteranl ms acess tables

P: n/a

I hope somebody can help me. I am collecting data from different
external ms access database on my VB programming. I am using the SQL
command as shown below:

While strPaths(iCtr) <> "" And iCtr <= MAX_ALLOW_DBASE
dbase.Execute "INSERT INTO DailySalesReport " & _
"SELECT table1.storeqs, table1.styleqs, table1.dateqs,
table1.idnumberqs, table1.cashqs, table1.salesclerkqs, table1.qs,
table1.ID " & _
"FROM table1" & _
"IN '' ';PWD=abcds;DATABASE=" & strPaths(iCtr) & "'" & _
"WHERE (((table1.storeqs) NOT IN (SELECT table1.storeqs FROM

where strPaths is an array that contains the absolute path of the
external database.

My problem is this:
I have two databases: Jan.mdb and Feb.mdb. Jan.mdb has 2 records in
table1 while Feb.mdb has 4 records on table1. All table1.storeqs in
Jan.mdb has the same value = JanStore while table1.storeqs in Feb.mdb
has the same value = FebStore. I have another similar database called
Mar.mdb where table1 is empty. This is where the data will be
collected. Running the SQL gives different results on Mar.mdb
depending on which table i accessed first. When i first collect data
from Jan.mdb, the result on table1.storeqs of Mar.mdb is shown below:
When I collect data from Feb.mdb first, the result is shown below:
The total number of entries on Mar.mdb in table1 should be 6.
However, it seems that the number of entries after collection is
dependent on the total number of entries of the table that has the
maximum number of entries. Since Feb.mdb has 4 entries (and Jan.mdb
has only 2), the result on Mar.mdb follows the total number of entries
in Feb.mdb. How did this happen? How can i make it collect all the
entries from Jan.mdb and Feb.mdb so that the total number of entries
on table1 of Mar.mdb will be 6? Please help.

Nov 13 '05 #1
Share this question for a faster answer!
Share on Google+

This discussion thread is closed

Replies have been disabled for this discussion.