Inserting Excel Data Range into Access Table

  • Thread starter MikeElectricUtility
  • Start date
M

MikeElectricUtility

I saw a post from K_Dale which provided assistance in Inserting data from a
data range into an Access Table.

He used a Function to loop through the range for the INSERT INTO [TableName]
Can someone please review my Procedure and Function to see where I have an
error?

In the Function BuildSQL I get a Compile Error: "For Each control variable
must be Variant or Object"

Some Background
Access Table = [Inventory Export], which has 3 columns and are defined to
match Excel columns
Excel Range = Worksheets("InvExport").Range("A29:C33)
Sample Range data
W0214071 411 2
W0214071 412 1
W0214071 413 2
W0214071 414 3

Sub UploadData()

Dim TableName As String
Dim ValueRange As Range

Dim MyCn As ADODB.Connection

Set MyCn = New ADODB.Connection

MyCn.Open "DRIVER={Microsoft Access Driver (*.mdb)};" _
& "DBQ=Z:\El Dorado Springs Inventory.mdb"

MyCn.Execute BuildSQL(TableName, ValueRange)

MyCn.Close
Set MyCn = Nothing

End Sub

Function BuildSQL(TableName As String, ValueRange As Range) As String

Dim DataCell As String
Dim SQL As String
Dim FirstCell As Boolean

TableName = "[Inventory Export]"
Set ValueRange = Worksheets("InvExport").Range("A29:C33")

SQL = "INSERT INTO " & TableName & " Values("

FirstCell = True

For Each DataCell In ValueRange
If Not (FirstCell) Then SQL = SQL & ","
SQL = SQL & "'" & DataCell.Text & "'"
FirstCell = False

Next DataCell

SQL = SQL & ")"

BuildSQL = SQL

End Function
 
M

merjet

Replace: Dim DataCell As String
With: Dim DataCell As Range

That addresses the error message. I didn't test otherwise.

Hth,
Merjet
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top