|
|
Our Hot Pick: Rising Antivirus 2006 - Certified by TUV & Checkmark! Get 10% discount by entering this coupon code: ONDISCOUNT10
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
having:
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.
|
|
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
CJ Brown wrote:
[Quoted Text] > 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
> having:
>
> 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.
|
|
"chris.nebinger[ at ]gmail.com" wrote:
[Quoted Text] > 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"
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
NULL"
qdTemp.ReturnsRecords = False
qdTemp.Execute dbFailOnError
' all those exectue OK
' but the below doesn't ...
qdTemp.SQL = "insert into archive select * from source"
qdTemp.execute
' 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.
|
|
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.
CJ
|
|
Glad I could help.
often times explaning the problem helps to solve it.
Chris Nebinger
CJ Brown wrote:
[Quoted Text] > 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.
>
> CJ
|
|
|