Because this data is used by the users in excel and then it needs to be
uploaded into Access to be used with additional data already in access to run
reports, etc.

"exceluserforeman" wrote:

Why ACCESS? Why do you not just use EXCEL?

"Secret Squirrel" wrote:

Hello,
I have this code setup in Excel but I want to be able to upload multiple
rows at one time. Is there a way to incorporate this into my code? Also, is
there a way to upload cells that are blank as well? Right now it won't allow
me to upload cells that are blank. Can this be done?

Sub UploadData()

Dim MyCn As ADODB.Connection
Dim SQLStr As String

Set MyCn = New ADODB.Connection

MyCn.Open "DRIVER={Microsoft Access Driver (*.mdb)}; " & _
"DBQ=\\Bcar1\bcar-net\Quality-ISO\Quality-Control\BC-Quality-Control\BC
QA mdb\BC Quality Action
Database.mdb;SystemDB=\\Bcar1\bcar-net\Quality-ISO\Quality-Control\BC-Quality-Control\BC QA mdb\Sys\sys.mdw;" & _
"Uid=Admin;" & _
"Pwd=password;"
' Replace actual Access file path here
' Note: If database has userID and password, need to specify them here also
' by appending "; UID=MyUserID; PWD=MyPassword"
' - if trouble accessing the file do a net search for help on Connection
Strings

SQLStr = "INSERT INTO [Table1] " _
& "Values ('" & Range("A10").Value & "', '" & Range("B10").Value & "',
'" _
& Range("C10").Value & "', '" & Range("D10").Value & "', '" &
Range("E10").Value & "', '" _
& Range("F10").Value & "', '" & Range("G10").Value & "', '" &
Range("H10").Value & "', '" _
& Range("I10").Value & "', '" & Range("J10").Value & "', '" &
Range("K10").Value & "', '" _
& Range("L10").Value & "', '" & Range("M10").Value & "', '" &
Range("N10").Value & "', '" _
& Range("O10").Value & "', '" & Range("P10").Value & "')"
' NOTE: The above assumes all fields are TEXT data type, that is why the "'"s;
' might have trouble with other data types unless you match the format
expected
' by the database

MyCn.Execute SQLStr

MyCn.Close
Set MyCn = Nothing

End Sub