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

collect data from exteranl ms acess tables

P: n/a
Hi,

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
table1)))"
Wend

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:
JanStore
JanStore
FebStore
FebStore
When I collect data from Feb.mdb first, the result is shown below:
FebStore
FebStore
FebStore
FebStore
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.

nick_faye
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.