Interacting with another Access DB in VBA

[redFred]

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_savings_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

 

[petehilljnr]

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.

 

[redFred]

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

 

[EyEciTy]

Sorry to reply on this post, but i can't yet put new threads and need urgent help please:

The code below is working but i want i don't need to open a new database.

Can someone help me with the code change, i need this connection but to use on a open database.

And i can't use Currentdb because i want to link to another database that is opened too.

Private Sub Command25_Click()
Dim strAppName
Dim strAppFrontEnd
Dim db As Access.Application
Set db = CreateObject("Access.Application")
strAppName = "Complaints1"
strAppFrontEnd = "Complaints07.mdb"
Set fso = CreateObject("Scripting.FileSystemObject")
Set oShell = CreateObject("Wscript.Shell")
' Get User Profile
strUserProfile = oShell.ExpandEnvironmentStrings("%USERPROFILE%")
pathdb = strUserProfile & "\" & strAppName & "\" & strAppFrontEnd
db.OpenCurrentDatabase (pathdb)
teste = db.Forms!Gestion!txtTextoReclamacion
End Sub

 

[EyEciTy]

Please help above please

 

[petehilljnr]

What are you trying to achieve?

 

[EyEciTy]

Hi Pete thanks for your reply.

I have two dbs, and i need to copy information of one db to my main db.
The code presented above is pointing to the other Db, that i have a vbs script that copies the db to mydocuments folder, this way all my colleagues can have the database on same folder and i can make a default path for the other db.

The code above works but when it executes it opens a new database instead of using the database that is opened already.
the problem is in the code below, there is another so i can use the database open instead of opening a new one?
db.OpenCurrentDatabase (pathdb)

Thank you

 

[spikepl]

Set appAccess = GetObject(PathOf AccessDb)

gets you the open db or opens it if not yet open. This works for full Access installations, but not for Runtime.

 

[EyEciTy]

it WORKED THANK YOU