Data from Excel to Access

G

Guest

Hello,
I have this code setup in Excel but I want to be able to upload multiple
rows at one time into a table. Is there a way to incorporate this into my
code? Maybe a loop statement??

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
 
D

David Lloyd

I don't know all the specifics of your setup, however, rather than "pushing"
data from Excel to Access, another alternative is to "pull" data from Excel
into Access. For example, you could use an INSERT INTO ...SELECT SQL
statement to pull Excel data from an entire worksheet, or just a range. The
following KB article gives more information about how to use Excel as a data
source in ADO.

http://support.microsoft.com/default.aspx?scid=kb;en-us;257819

--
David Lloyd
MCSD .NET
http://LemingtonConsulting.com

This response is supplied "as is" without any representations or warranties.


message Hello,
I have this code setup in Excel but I want to be able to upload multiple
rows at one time into a table. Is there a way to incorporate this into my
code? Maybe a loop statement??

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
 
G

Guest

I will review the link you sent. I agree that pulling the data is much easier
but in the meantime is there a simple fix I can do to my code to get it to
push more than just one row? Right now I have a command button on my
worksheet that I use to upload the data to an access table. Everything works
fine according to my code I listed earlier. All I want to do is add a loop
statement to upload more than one row at a time. Can you help shed some light
I don't know all the specifics of your setup, however, rather than "pushing"
data from Excel to Access, another alternative is to "pull" data from Excel
into Access. For example, you could use an INSERT INTO ...SELECT SQL
statement to pull Excel data from an entire worksheet, or just a range. The
following KB article gives more information about how to use Excel as a data
source in ADO.

http://support.microsoft.com/default.aspx?scid=kb;en-us;257819

--
David Lloyd
MCSD .NET
http://LemingtonConsulting.com

This response is supplied "as is" without any representations or warranties.


message Hello,
I have this code setup in Excel but I want to be able to upload multiple
rows at one time into a table. Is there a way to incorporate this into my
code? Maybe a loop statement??

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
Click to expand...
 
D

David Lloyd

There are a few alternatives for looping. Given your current code, I think
the easiest would be something like the following:

Dim i as Integer

For i = 10 to 20 'Choose your own upper limit
SQLStr = "INSERT INTO [Table1] " _
& "Values ('" & Range("A" & CStr(i)).Value & "', '" & Range("B" &
CStr(i) ).Value & "',
..
..
..

Next i

You could also use the ActiveCell.Offset property to move down the rows.
For example:

Dim i as Integer

Cells(10, 1).Select 'Select cell A10

For i = 10 To 20
SQLStr = "INSERT INTO [Table1] " _
& "Values ('" & ActiveCell.Value & "', '" & ActiveCell.Offset(0,1).Value
& "',
..
..
..
ActiveCell.Offset(1, 0).Select 'Move down one row on Column A
Next i


-
David Lloyd
MCSD .NET
http://LemingtonConsulting.com

This response is supplied "as is" without any representations or warranties.


message I will review the link you sent. I agree that pulling the data is much
easier
but in the meantime is there a simple fix I can do to my code to get it to
push more than just one row? Right now I have a command button on my
worksheet that I use to upload the data to an access table. Everything works
fine according to my code I listed earlier. All I want to do is add a loop
statement to upload more than one row at a time. Can you help shed some
light
I don't know all the specifics of your setup, however, rather than
"pushing"
data from Excel to Access, another alternative is to "pull" data from
Excel
into Access. For example, you could use an INSERT INTO ...SELECT SQL
statement to pull Excel data from an entire worksheet, or just a range.
The
following KB article gives more information about how to use Excel as a
data
source in ADO.

http://support.microsoft.com/default.aspx?scid=kb;en-us;257819

--
David Lloyd
MCSD .NET
http://LemingtonConsulting.com

This response is supplied "as is" without any representations or
warranties.


message Hello,
I have this code setup in Excel but I want to be able to upload multiple
rows at one time into a table. Is there a way to incorporate this into my
code? Maybe a loop statement??

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
Click to expand...
 
G

Guest

David,
I tried inserting that into my code but now I'm getting an error message:

[Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in
query expression '128','10/13/2005')'.

Any idea what this means?

Also, I have two other questions about my code;

1. Do you know how to add code that will stop uploading when it comes to a
blank row? For example when it is uploading the rows and then it finds a cell
in column "A" blank then it knows to stop uploading.

2. Do you know how to tell it to also overwrite the table in access? If a
user uploads more than once all it does is add it to the table. I want it to
overwrite it so there isn't any duplicate information in there.

Thanks for you help!

David Lloyd said:
There are a few alternatives for looping. Given your current code, I think
the easiest would be something like the following:

Dim i as Integer

For i = 10 to 20 'Choose your own upper limit
SQLStr = "INSERT INTO [Table1] " _
& "Values ('" & Range("A" & CStr(i)).Value & "', '" & Range("B" &
CStr(i) ).Value & "',
..
..
..

Next i

You could also use the ActiveCell.Offset property to move down the rows.
For example:

Dim i as Integer

Cells(10, 1).Select 'Select cell A10

For i = 10 To 20
SQLStr = "INSERT INTO [Table1] " _
& "Values ('" & ActiveCell.Value & "', '" & ActiveCell.Offset(0,1).Value
& "',
..
..
..
ActiveCell.Offset(1, 0).Select 'Move down one row on Column A
Next i


-
David Lloyd
MCSD .NET
http://LemingtonConsulting.com

This response is supplied "as is" without any representations or warranties.


message I will review the link you sent. I agree that pulling the data is much
easier
but in the meantime is there a simple fix I can do to my code to get it to
push more than just one row? Right now I have a command button on my
worksheet that I use to upload the data to an access table. Everything works
fine according to my code I listed earlier. All I want to do is add a loop
statement to upload more than one row at a time. Can you help shed some
light
I don't know all the specifics of your setup, however, rather than
"pushing"
data from Excel to Access, another alternative is to "pull" data from
Excel
into Access. For example, you could use an INSERT INTO ...SELECT SQL
statement to pull Excel data from an entire worksheet, or just a range.
The
following KB article gives more information about how to use Excel as a
data
source in ADO.

http://support.microsoft.com/default.aspx?scid=kb;en-us;257819

--
David Lloyd
MCSD .NET
http://LemingtonConsulting.com

This response is supplied "as is" without any representations or
warranties.


message Hello,
I have this code setup in Excel but I want to be able to upload multiple
rows at one time into a table. Is there a way to incorporate this into my
code? Maybe a loop statement??

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
Click to expand...
Click to expand...
 

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

Similar Threads

Syntax error in INSERT INTO statement 10
Data from Excel to Access Table 12
Excel export to Access - Run-time error 1004 6
VBA Excel - Select a value from one cell in Excel to update a SQL table 1
exporting to excel 21
ODBC Run-Time Error Handling Help 5
Inserting Excel Data Range into Access Table 2
Data Upload from Excel to SQL Server 3

Top