Insert query from Access 97 to 2002

[Dale Matt]

We are transitioning an app from Access 97 to Acess 2002. For the
foreseeable future an insert query in an Access 97 app needs to insert data
into a table in an Access 2002 app (previously this query worked between two
Access 97 apps).

When I run the query I get an "Unrecognized Database Format" error.

Any suggestions on a good way around this that still allows me to run my
Access 97 and Access 2002 apps as they are currently situated?

Thanks in advance...

 

[david]

1) Normal method. Create a Front-End / Back-End application with
the Front-End in Access 2002 and the Data in a Jet 3.5 (Access 97)
database.

2) Obscure method. Replace the insert query with code that runs
an insert query. Use a dbengine.36 object in the code to execute
the SQL you need for the insert query.

3) Perverse method. Create a new SQL Server database. In it,
create tables linked to the new 2002 database. Link the A97
application to SQL Server linked tables.

(david)

 

[Dale Matt]

Thanks, David. The obscure method sounds like it might work best, but I'm
not sure what you mean by a dbengine.36 object. The code involved is as
follows:

Set db = DBEngine.Workspaces(0).Databases(0)
Set qry = db.CreateQueryDef()
qry.Name = "qrytemp"
db.QueryDefs.Append qry
qry.sql = sqlstr
qry.Execute

I'm assuming you're referring to a manipulation of the first line?

 

[david]

set obj = createobject("dbengine.36")
set ws = obj.openworkspace(
set db36 = ws.opendatabase( new jet 3.6 database

db36.execute(sqlstr)

Now we have two databases open, the CurrentDB, which
is Access 97, and this other db36. which is not connected,
it is in a new object.

But this other db36 can run append queries which append
from an A97 application to the A2002 application. It doesn't
matter that you have the A97 application open, as long as
you don't have it open in Exclusive Mode.


old sqlstr, assuming 1 database:
INSERT INTO fred SELECT Table1.* FROM Table1;

new sqlstr, executed in new database, with path to old database:
INSERT INTO fred SELECT * FROM [olddb].Table1;

You can get the source (97) database name and path from
CurrentDB.name

I don't know why you are using a named querydef, instead of
an unnamed querydef or db.execute, but you can continue to do
that if it is required.

(david)

 

[Dale Matt]

David, you rock. Thanks a bunch...

set obj = createobject("dbengine.36")
set ws = obj.openworkspace(
set db36 = ws.opendatabase( new jet 3.6 database

db36.execute(sqlstr)

Now we have two databases open, the CurrentDB, which
is Access 97, and this other db36. which is not connected,
it is in a new object.

But this other db36 can run append queries which append
from an A97 application to the A2002 application. It doesn't
matter that you have the A97 application open, as long as
you don't have it open in Exclusive Mode.


old sqlstr, assuming 1 database:
INSERT INTO fred SELECT Table1.* FROM Table1;

new sqlstr, executed in new database, with path to old database:
INSERT INTO fred SELECT * FROM [olddb].Table1;

You can get the source (97) database name and path from
CurrentDB.name

I don't know why you are using a named querydef, instead of
an unnamed querydef or db.execute, but you can continue to do
that if it is required.

(david)

Thanks, David. The obscure method sounds like it might work best, but I'm
not sure what you mean by a dbengine.36 object. The code involved is as
follows:

Set db = DBEngine.Workspaces(0).Databases(0)
Set qry = db.CreateQueryDef()
qry.Name = "qrytemp"
db.QueryDefs.Append qry
qry.sql = sqlstr
qry.Execute

I'm assuming you're referring to a manipulation of the first line?