Updating Database on Server from multiple databases on C:\ with MS Access
Here is the problem: On everyone's C:\ is an MS Access database that is linked to our MRP system JobBoss. I have created a custom program for producing inventory labels. I need to capture each part that is labeled in a central database so I can monitor progress, check for problems, etc. I am unable to update the database on the server. I do not get any error messages. Simply the table on the server is never updated.
I've tried a couple of different methods:
Dim wrk As Workspace
Dim db2 As DAO.Database
Dim rst As Recordset
Dim conRemote As Connection
(code to do other things)
'- Updated database on server
Set wrk = DBEngine(0)
Set conRemote = wrk.OpenConnection("\\server3\Users\Accounts\2006 BOMs\Labeled_Parts.mdb")
Set db2 = conRemote.OpenDatabase("\\server3\Users\Accounts\2006 BOMs\Labeled_Parts.mdb")
Set rst = db2.OpenRecordset("tblLabeled", dbOpenDynaset)
With rst
.AddNew
rst!Material = [Forms]![frmEnter_PN].[txtPartNumber]
.Update
End With
Set rst = Nothing
db2.Close
End sub
or I tried:
Dim wrk As Workspace
Dim db As DAO.Database
Dim rst As Recordset
Dim strSQL As String
strSQL = "INSERT INTO tblLabeled ( Material, Description, iPO, " & _
"strDrawing, [Prior$], Vendor, Order_Date, Issued_By ) " & _
"SELECT Canter_Temp.Material, Canter_Temp.Description, Canter_Temp.iPO, " & _
"Canter_Temp.strDrawing, Canter_Temp.[Prior$], Canter_Temp.Vendor, " & _
"Canter_Temp.Order_Date, Canter_Temp.Issued_By FROM Canter_Temp;"
Set wrk = DBEngine(0)
Set db = wrk.OpenDatabase("\\server3\Users\Accounts\2006 BOMs\Labeled_Parts.mdb")
db.Execute strSQL
db.Close
Neither one worked. It appears I just can't see the database Labeled_Parts.mdb on the server
Any suggestions?
