I have developed a Excel Application that inserts data from Excel to an Access Database via SQL.
The code works on a PC with Access, however the users whom i am building this app for don't have MS Access.
There are around 5 users who will be submitting data from their Excel file into the .mdb.(without MS Acccess on thier PC's)
Can somebody please help me, i believe that i can use the Jet engine to insert data from Excel to the MDB file without MS Access on the PC, however i don't know how this can be done, don't know the code that's involved.
*Also please note that i can't use the ADO object as i have restricted access to this object at work.
Please help asap, appreciate help on this and i am keen on sharing my experiences and skills with others.
Here is the code that inserts data from Excel to the .mdb file with MS Access.
The Test.mdb file is just a file consisting of 2 fields(name,surname)
''strSQLCommand1 = "INSERT INTO test VALUES ('" & var1 & "', '" & var2 & "')"
Dim appAccess As Object
Dim strConPathToSamples As String
Dim strDB As String
Dim var1 As String
Dim var2 As String
' Initialize string to database path.
strConPathToSamples = "\\svrau227trs00.oceania.corp.anz.com\jonuzij1 $\My Documents\M-drive data\Decision Support System Project\Merchant\v1\test.mdb"
strDB = strConPathToSamples
' Create new instance of Microsoft Access.
Set appAccess = _
' Open database in Microsoft Access window.
' Open Orders form.
appAccess.DoCmd.RunSQL "(insert into test values('" & var1 & "', '" & var2 & "'))"
The interface to Jet is known as Data Access Objects (DAO). Your users should have this installed already. Under Tools...References look for 'Microsoft DAO Object Library 3.6' or whatever version. Make sure your users have the same version too. You can look up DAO programming in the online help files, but here's some sample code to get you started:
Dim db As DAO.Database
Dim rs As DAO.Recordset
Set db = DAO.DBEngine.OpenDatabase("C:\Program Files\Microsoft Office\Office10\Samples\northwind.mdb")
Set rs = db.OpenRecordset("Customers", dbOpenDynaset)
rs!CustomerID = "ANYCO"
rs!CompanyName = "A New Co"
rs!City = "AnyTown"
rs!Region = "BigArea"
rs!PostalCode = "12345"