Pass-thru vs non-pass thru query in DAO

Pass-thru vs non-pass thru query in DAO

Post by Q0ogQnJvd2 » Sat, 23 Sep 2006 00:46:01

I'm working in Access97 & DAO, unfortunately, as there are upgrade issues
stopping me from using something more up to date. Here's the problem I'm

I'm updating data in a A97.mdb (lets say "archive.mdb") with info from a
.csv file, from code in another A97.mdb (lets say "control.mdb"). Control.mdb
has some static data tables in it. To do the transfer, I'm creating a temp
database in c:\temp, and linking the 3 parts of my data transfer to it as 3
linked tables - archive, source and, say, lookup. Now, I can do select and
delete queries on these tables just fine in DAO, and when I open this temp db
by hand I can do what I want to do by hand just fine using a query - insert
into archive select * from source where (condition on lookup). Doing this by
hand by means of a query is fine. Yet when I try to do the same thing using
DAO, it treats my query as a pass-thru query and wants a (ODBC)connection
string - I'm transferring data between 3 linked tables, so I can't see why it
is treating this as a pass-thru, when doing so manually appears to treat the
query as being an intra-database object.

I can code around this eg I can open the archive database and bring the data
into it directly, do my processing on it, and so on ... but I'd like to know
what is going on, and why it won't work doing it this way, in particular why
when I create queries by hand and by DAO there is the different treatment -
why the DAO ones are coerced into being pass-thrus. I'm very rusty, but I can
remember doing this sort of thing extensively a few years back.

Pass-thru vs non-pass thru query in DAO

Post by chris.nebi » Sat, 23 Sep 2006 01:24:32

I think that you are not quite performing the method correctly. This
code works for me:

Dim dbs As DAO.Database
Set dbs = Workspaces(0).OpenDatabase("C:\Documents and
Settings\user\My Documents\db1.mdb")
Dim tdf As DAO.TableDef
For Each tdf In dbs.TableDefs
Debug.Print tdf.Name
Next tdf
dbs.Execute "Delete * from Table1"

Chris Nebinger


Pass-thru vs non-pass thru query in DAO

Post by Q0ogQnJvd2 » Sat, 23 Sep 2006 01:37:01

OK, I'll explain a bit more, and include some code ...

' connect to archive DB via temp db

Set tdTemp = dbTemp.CreateTableDef("archive", 0, "ta_archive",
";DATABASE=" & sArchiveDB)
dbTemp.TableDefs.Append tdTemp

' connect to info file via temp db

Set tdTemp = dbTemp.CreateTableDef("Source")
tdTemp.Connect = "Text;DATABASE=C:\Temp"
tdTemp.SourceTableName = "info.csv"
dbTemp.TableDefs.Append tdTemp

' connect to the static table in this app

Set tdTemp = dbTemp.CreateTableDef("Desks", 0, "Eq_VAR_books",
";DATABASE=" & CurrentDb.Name)
dbTemp.TableDefs.Append tdTemp

' All the above works fine - eg ...

qdTemp.SQL = "SELECT mydate FROM Source WHERE desk <> NULL GROUP BY mydate"
Set rsTemp = qdTemp.OpenRecordset
rsTemp.MoveLast: rsTemp.MoveFirst

qdTemp.SQL = "delete from archive where date = #" & sDate & "# and desk is
qdTemp.ReturnsRecords = False
qdTemp.Execute dbFailOnError

' all those exectue OK
' but the below doesn't ...

qdTemp.SQL = "insert into archive select * from source"

' this throws a pass-thru connection string error
' BUT if the same thing is done by hand in the temp db, it runs fine

Thanks for looking. CJ.

Pass-thru vs non-pass thru query in DAO

Post by Q0ogQnJvd2 » Sat, 23 Sep 2006 03:38:02

You figure it out yourself eventually ...

Instead of creating a QueryDef, and then having VBA/DAO throw a wobbly about
pass-thru connections, etc, the way to get this done was to execute the SQL
directly against the database ie

dbTemp.execute "insert into archive select * from source where yadda yadda
yadda", dbFailOnError
msgbox dbTemp.RecordsAffected & " rows copied"

I knew there should be a way to execute the SQL, as A97 was fine with it a
manually constructed query.

Nice & neat. Thanks for looking.


Pass-thru vs non-pass thru query in DAO

Post by chris.nebi » Sat, 23 Sep 2006 10:41:39

Glad I could help.

often times explaning the problem helps to solve it.

Chris Nebinger