I have a db open and the form in which data is entered, I added a button to accomplish the following:

1. open another database instance
2. addnew record set
3. add data to field in new recordset
4. update & close the other database instance

I am getting the error that the Jet can not find the table specified …….. I did some debugging and believe that the currentdb is not the new instance, please review my code for suggestions on where I am going wrong

thanks


Sub DisplayForm()
' Initialize string to database path.
Const strConPathToSamples = "H:\Ase_Rde\Databases\Cost_RDE\Sta_Rot_BH_Tobi_savi ngs_Database.mdb"

strDB = strConPathToSamples & "Sta_Rot_BH_Tobi_savings_Database.mdb"
' Create new instance of Microsoft Access.
Set appAccess = _
CreateObject("Access.Application")
' Open database in Microsoft Access window.
appAccess.OpenCurrentDatabase strConPathToSamples

' not sure if next is needed
'Application.RefreshDatabaseWindow

Dim rst As DAO.Recordset
Dim db As DAO.Database
Set db = CurrentDb()

Set rst = db.OpenRecordset("tblMain", dbOpenDynaset)

With rst
.AddNew

![REA] = "1234"
.Update
.Move 0, .LastModified
End With

rst.Close
' close the database instance
appAccess.CloseCurrentDatabase

'clean the varible

Set rst = Nothing
Set appAccess = Nothing
Set db = Nothing
MsgBox "Done"

End Sub

You don't need to open an entire Access instance do you?

How about this:

Dim db as DAO.Database
Dim rs as DAO.Recordset

Set db = Opendatabase("PATH AND FILENAME TO DB.mdb")

Set rs = db.Openrecordset("NAME OF TABLE")

rs.AddNew
rs! .... = .....
rs.Update

rs.Close
db.Close

I also noticed your path to your db will be wrong as you've mentioned the filename twice.

Regards,
Pete.

thanks....the following code works if anyone is interested:

_______________________
'now open the instance to the cost db and upload the data

Dim acc As Access.Application
Dim db As DAO.Database
Dim strDbName As String
Dim rst As DAO.Recordset

strDbName = "J:\Repair_Development\HDW-Stators-Rotating-BH-TOBI\Data Base\Sta_Rot_BH_Tobi_savings_Database.mdb"

Set acc = New Access.Application
Set db = acc.DBEngine.OpenDatabase(strDbName, False, False)
acc.OpenCurrentDatabase strDbName
Set rst = db.OpenRecordset("RDE TASKS", dbOpenDynaset)

' upload the data
With rst
.AddNew
![REA-IEN] = REA
' etc
.Update
.Move 0, .LastModified
End With

rst.Close
' close the database instance
db.Close

'clean the varibles
Set rst = Nothing
Set appAccess = Nothing
Set db = Nothing
MsgBox "Done"

End Sub