merge multiple databases

  • Thread starter Thread starter DIOS
  • Start date Start date
Jim Carlock said:
If you put the SQL into queries inside of Access, I'm thinking that
it should get even faster... IE make the query in the new Access file,
and then call that query from the VB application, with an .Execute
if it's an action query, or with the .RecordSet object if it's a Select
query.

If the query inside of Access was named "qSelectAllRecords", then
you can open it with :

Set rs = gDAO_DB.OpenRecordset("qSelectAllRecords")
Click to expand...

I've done some half assed testing with that in the past, and never really
noticed a difference. But again, it was half assed, so you may be right.

Matt
 
I think there are other things that might play into it as well.
When I did it, it was through an ODBC connection, and
maybe I just fell into the fact that the Internal Access query
was faster, and perhaps it was just easier to read for me.

There were a few thrings I tried but I don't remember
exactly if that specifically was one of them. I ended up
with an internal Access Make Table query and then a
ton of update queries. The biggest problem I had was
getting the download of the initial tables and I had tried
doing selective queries but that ended up taking over 24
hours, I tried VB SQL and decided that using the Access
queries was much better for some reason. I left some
VB string SQL in place for delivering the data to the
report generator, as there were 1000s of accounts and
some some accounts with 1000s of transactions that
needed printing.

I'm kind of hoping DIOS tries it out and gives a Yay or
Nay.

--
Jim Carlock
http://www.microcosmotalk.com/
Post replies to the newsgroup.


:
Jim Carlock said:
If you put the SQL into queries inside of Access, I'm thinking that
it should get even faster... IE make the query in the new Access file,
and then call that query from the VB application, with an .Execute
if it's an action query, or with the .RecordSet object if it's a Select
query.

If the query inside of Access was named "qSelectAllRecords", then
you can open it with :

Set rs = gDAO_DB.OpenRecordset("qSelectAllRecords")
Click to expand...

I've done some half assed testing with that in the past, and never really
noticed a difference. But again, it was half assed, so you may be right.

Matt
 
Well Im using DAO and the results were conclusive as far as im
concerned. The user will not use this function a whole lot but I was
asked to implement it and the code turned out to be about 8 SQL
statements and was a lot more concise than the record looping code.
Thanx for the direction I needed on this code.

AGP
 
Well, as YYZ said, I myself tried built in queries in the Access database
but did not notice much difference. Maybe there is a difference on bigger
databases but mines is for a home program with maybe like 50 to 100 thousand
records. Also, since the user does not even know that the database is an
Access
databases, I need to do everything through DAO. At this point I dont want to
mess
around with inseeting queries in Access and then running them from there.
For
the moment the results were satisfactory. The test I maded was probably an
extreme
end so im looking at 20 to 60 seconds for the user to merge his databases.
i aslo walk him through every step and I dont think they are going to
complain.

Thanx
AGP
 
Back
Top