how to transfer data from excel to access with a macro in excel?

[Iggy]

Hi,

Let's say I have a customer's name entered in Sheet1 at A1 (file name
(MyExcel)). And there is button right by it. I want to assign a macro to that
button that it can transfer this name to my access file called MyAccess
(assuming that I have a table called "customers" and it has a one field
called "Name").

What should be the code for tha macro I want to assign to the button in Excel?

Thanks.

 

[John Nurick]

One way is to use something like this air code, in the button's Click
event procedure.

Dim oJet As Object 'DAO.DBEngine.36
Dim oDB As Object 'DAO.Database
Dim strName As String
Dim strSQL As String

Const DB_NAME = "C:\folder\MyAccess.mdb"

'Create instance of Jet database engine and open the database
Set oJet = CreateObject("DAO.DBEngine.36")
Set oDB = oJet.OpenDatabase(DB_NAME)

'Get customer name from cell
strName = ActiveWorkbook.ActiveSheet.Cells(1,1).Value
'Build SQL single-record append query
strSQL = "INSERT INTO customers (Name) " _
& "VALUES ('" & strName & "');"
'Execute it
oDB.Execute strSQL, 128 '128=dbFailOnError

'Close the database
oDB.Close