SQL Insert from Excel into Access [Archive]

jonuzij1

09-05-03, 00:33

HI,

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)

-----------------------------

Sub SQLInsert1()

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

var1 = Sheets("sheet1").Cells(1, 1)
var2 = Sheets("sheet1").Cells(1, 2)

' 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
MsgBox strDB

' Create new instance of Microsoft Access.
Set appAccess = _
CreateObject("Access.Application")
' Open database in Microsoft Access window.
appAccess.OpenCurrentDatabase strConPathToSamples
' Open Orders form.
appAccess.DoCmd.RunSQL "(insert into test values('" & var1 & "', '" & var2 & "'))"

End Sub

YAU

09-08-03, 10:15

Hi jon

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:

Sub AddToMDB()

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.AddNew
rs!CustomerID = "ANYCO"
rs!CompanyName = "A New Co"
rs!City = "AnyTown"
rs!Region = "BigArea"
rs!PostalCode = "12345"
rs.Update

Set db = Nothing
Set rs = Nothing

End Sub

HTH

YAU