Excel data to Access database

[Les]

I have an excel sheet that I am using kind of like a front end GUI. Of the
data on the worksheet lets say, for example, I need 3 pieces of data on it,
data1, data2 and data3 that I want to send to an Access database upon
clicking a command button on the excel worksheet. I need the 3 pieces of
data to be appended to the end of the access database. I know very little
about vba but have been programming for some time. If someone can supply
with the necessary code, I assume it cant be to long, I would appreciate it.

Thanking you in advance,

Les

 

[Steve]

If the data is in a range, look at the TransferSpreadsheet method in the Help
file.

 

[Les]

Hi Steve,

I will look up your suggestion but I do not believe it is in a range but wil
always be in the same place each and every time (ie cells a3, b7,d9). Any
advice if it is set up this way?

Thanks,

Les

 

[John Nurick]

Hi Les,

Try something like this. You'll need to set a reference in the Excel
project (Tools|References) to the Microsoft DAO 3.x object library. If
you are appending to text fields in the Access table, you'll need to put
quote marks round the values from Excel as indicated below for the field
"Text2"); or omit them for numeric fields (as for fields "Number1" and
"Number 2". You'll also need (of course) to substitute the row and
column numbers of the actual cells you want.

Sub AppendTest()
Dim dbE As DAO.DBEngine
Dim dbD As DAO.Database
Dim strSQL As String

Set dbE = New DAO.DBEngine
Set dbD = dbE.OpenDatabase("C:\folder\file.mdb")

'build the SQL append query
With Application.ActiveSheet
strSQL = "INSERT INTO MyTable " _
& (Number1, Text2, Number3) " _
& "VALUES (" _
& .Cells(1, 1).Value & ", " _
& """" & .Cells(1, 2).Value & """, " _
& .Cells(1, 3).Value & ");"
End With
'execute it
dbD.Execute strSQL, dbFailOnError

'tidy up
dbD.Close
Set dbD = Nothing
Set dbE = Nothing
End Sub