Make-table Query - Destination DB question

[Scott]

Hi,

Is there an easy way of changing the output database (i.e. Destination
DB) in several make-table queries that export data to a separate DB? I
ask as we are in the process of migrating an Access 2000 DB onto a
number of new office servers which will have different path names, so
what was along the lines of g:\..\Database could become j:\..\Database,
but we don't want to have to go in and manually change the SQL code for
all the queries... is there a quick way of doing this, or could we set
the destination DB as a field that could be changed once and replicated
through all the queries?

Sorry if this is a little vague, but these boards have helped me out on
several occasions before!

Scott

 

[Rick Wannall]

There is no way to vary the destination DB specification in a query other
than rewriting the SQl for the query on the fly.

That said, you can use vb to get to the sql of your query and do anything
you want. If your export process is run by a stream of vb code, you can use
a database object (as DAO.database) to get a querydef object (as
DAO.querydef) and in code get to the sql of the querydef. From there you
can parse to replace the database path with a new one, or you can store the
core SQL as a constant in the vb code with a placeholder that you replace.
For example:

const MYSQL as string = "Select.....Into [mydatabasepath]..."

dim db as dao.database
dim qd as dao.querydef
dim s a sql
set db = currentdb
set qd = db.querydefs("myquery")
qd.sqls = replace(MYSQL, "[mydatabasepath]", "d:\xxxx\yyy\abcd.mdb")

Ready to go.

 

[Rick Wannall]

There is no way to vary the destination DB specification in a query other
than rewriting the SQl for the query on the fly.

That said, you can use vb to get to the sql of your query and do anything
you want. If your export process is run by a stream of vb code, you can use
a database object (as DAO.database) to get a querydef object (as
DAO.querydef) and in code get to the sql of the querydef. From there you
can parse to replace the database path with a new one, or you can store the
core SQL as a constant in the vb code with a placeholder that you replace.
For example:

const MYSQL as string = "Select.....Into [mydatabasepath]..."

dim db as dao.database
dim qd as dao.querydef
dim s a sql
set db = currentdb
set qd = db.querydefs("myquery")
qd.sqls = replace(MYSQL, "[mydatabasepath]", "d:\xxxx\yyy\abcd.mdb")

Ready to go.

 

[Allen Browne]

Scott, you can read the SQL property of a querydef, Replace() any occurrance
of the string, and assign it back to the QueryDef.

This kind of thing:
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Set db = CurrentDb()
For each qdf in db.QueryDefs
If Instr(qdf.SQL, "g:\..\Database") > 0 Then
qdf.SQL = Replace(qdf.SQL, "g:\..\Database", "j:\..\Database")
End If
Next
Set db = Nothing

 

[Allen Browne]

Scott, you can read the SQL property of a querydef, Replace() any occurrance
of the string, and assign it back to the QueryDef.

This kind of thing:
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Set db = CurrentDb()
For each qdf in db.QueryDefs
If Instr(qdf.SQL, "g:\..\Database") > 0 Then
qdf.SQL = Replace(qdf.SQL, "g:\..\Database", "j:\..\Database")
End If
Next
Set db = Nothing

 

[(PeteCresswell)]

Per Scott:

Is there an easy way of changing the output database (i.e. Destination
DB) in several make-table queries that export data to a separate DB?

If you find an easy way, let me know....-)

When I converted to doing work tables the proper way (storing them under
[UserName]....) I gave up and just replaced the few MakeTables with a generic
routine "CreateWorkTable" that copies a model into the work TB and creates a
link to it... and then used Append queries.

Maybe somebody can elucidate, but I can't think of any advantage to using
MakeTable except for PivotTable presentations where the column names vary
depending on data.

 

[(PeteCresswell)]

Per Scott:

Is there an easy way of changing the output database (i.e. Destination
DB) in several make-table queries that export data to a separate DB?

If you find an easy way, let me know....-)

When I converted to doing work tables the proper way (storing them under
[UserName]....) I gave up and just replaced the few MakeTables with a generic
routine "CreateWorkTable" that copies a model into the work TB and creates a
link to it... and then used Append queries.

Maybe somebody can elucidate, but I can't think of any advantage to using
MakeTable except for PivotTable presentations where the column names vary
depending on data.

 

[Rick Wannall]

Copying a model table to a work DB is simple and clean.

Seconded.

 

[Rick Wannall]

Copying a model table to a work DB is simple and clean.

Seconded.