Save data in 2nd database thru VBA

  • Thread starter Thread starter Gary
  • Start date Start date
G

Gary

Developing an Access 97 'application' on Windows 2K
machine, I wish (thru VBA) to 'add' (before deletion)
specific records to a table ('tblDeleted') in ANOTHER
Access database ('Archive.mdb').

The specific records are from a Me.RecordSetClone
(displayed on a form whose RecordSource is a query based
on selected fields from two tables) in the active
('CurrentDb').

I've created the 2nd database ('Archive.mdb'), with the
empty table ('tblDeleted'), ready to receive the deleted
records from the first database as described above, and
have placed it in the SAME directory as my 1st database.

Regretfully, for some unknown reason, I get an ERROR
message indicating that Access cannot find 'Archive.mdb'.

In my attempt at 'troubleshooting', I copied the code
suggested in the following relevant example (from
the 'help' file in Access):

"The following example returns a Database variable that
points to the current database. Then it opens a different
database called Another.mdb by using the OpenDatabase
method. The procedure then enumerates all TableDef objects
in both databases.
To try this example, create a new database called
Another.mdb, close it, and place it in the same directory
as the database from which you are running the code."

================ Start of Code ================
Sub OpenAnother()
Dim wsp As Workspace
Dim dbs As Database, dbsAnother As Database
Dim tdf As TableDef

' Return reference to current database.
Set dbs = CurrentDb
' Return reference to default workspace.
Set wsp = DBEngine.Workspaces(0)
' Return reference to Another.mdb.
Set dbsAnother = wsp.OpenDatabase("Another.mdb")
' Enumerate all TableDef objects in each database.
Debug.Print dbs.Name & ":"
For Each tdf in dbs.TableDefs

Debug.Print tdf.Name
Next tdf
Debug.Print
Debug.Print dbsAnother.Name & ":"
For Each tdf in dbsAnother.TableDefs
Debug.Print tdf.Name
Next tdf
Set dbs = Nothing
Set dbsAnother = Nothing
End Sub

================ End of Code ================

I STILL get the SAME error: Access cannot find my file--
this time 'Another.mdb'.

Any helpful ideas or suggestions would be appreciated.

TIA,
Gary
 
Try putting the full path to "Another.mdb", instead of just

Set dbsAnother = wsp.OpenDatabase("Another.mdb")
 
Back
Top