transferdatabase method question

[Aivars]

Hello,

I am importing a query from a database as a table in another database
(.accdb) file.
I have the following code which works OK

DoCmd.TransferDatabase _
acImport, "Microsoft Access", _
strPathDB, _
acTable, _
"old_Final", "new_FINAL"

The question is - is this query (old_Final) updated /run before being
imported to the new .accdb file? I mean is the data I get fresh from
that another database?

Thanks
Aivars
Using Access 2007

 

[Guest]

Hi Aivars, yes the data transferred will be the result of your query when the
code is run. A query does not actually hold any data within it, all it
hold's is the structure.

HTH

Stu

 

[storrboy]

Queries don't actually contain data so anytime they are run, it can
only act on the data that exists at the time of running. So to the
best of my knowledge, when you transfer a query as a table it can only
be on the current data. That said if there is criteria in the query,
it may restrict the returned records and make it look like new data is
omitted.

 

[Aivars]

Thanks, storrboy,
I think I agree with you but I just wonder when that remote query is
run - is other database somehow opened in a background? And, if there
is a VBA function in that query - will it be calculated too? Of
course, I can try and experiment, just thought maybe somebody has
already been there and done that

Thanks,

Aivars
Using Access 2007

 

[storrboy]

You have to remember that Jet (or whatever data engine you are using)
is actually separate from Access. I don't know the specifics of how
Access db's read the contents of each file, but the data itself can
accessed without having to open the other database.

Yes, a function or any other recognized calculation is included.

 

[Aivars]

Thanks storrboy,
Sure, you are right. Before I was doing a lot of excel development
(kind of) and used to hold my data in an .mdb file without Access
involved at all. And it worked fine. Then i decided to give Access a
try and... I am not looking back anymore to Excel (which I like very
much).
Still not sure why VBA functions are working but if they do then who
cares why

Thanks
Aivars

 

[storrboy]

Still not sure why VBA functions are working but if they do then who
cares why

Kind of like asking the bomber pilot how the proximaty fuse is
designed.

 

[Aivars]

Hi, storrboy,
Just wanted to inform you that VBA functions used in query and this
query being imported into another database either as query or as table
are not recognized and imoprt fails.
Therefore, a workaround should be worked out. I think its better then
to keep all in one accdb file (in spite a lot tables being there in my
particular case) or maybe move all data tables to SQL server and
write stored procedures there and call them from Access if thats
possible. I am not familiar with sql server (but have recently
installed sql 2005 express for evaluation).
Thanks
Aivars

 

[storrboy]

Are you talking about predefined or user defined functions?
I was thinking (perhaps incorrectly) you were talking about the
former, and I would be surprised if they did not work as they should
be available in all instances of Access. I can see were user defined
ones would fail as it would need to be available in the importing db
before the query is run.

If that's the case, then ensuring the functions are in the destination
db before doing the import should solve it. Moving to Sql05 may also
give you some added capabilities once you become more familiar with
it. (I'm not however.)

 

[Aivars]

sorry,
I shud have mentioned that i was talking about UDF (user defined
functions) VBA functions of course. And your suggestion about keeping
UDF VBA functions in the destination database sure is a valuable one.
Thank you

aivars