| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Data from Excel to Access Table
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 |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Data from Excel to Access Table
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 |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Data from Excel to Access Table
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 |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Data from Excel to Access Table
Then maybe the stuff in ACCESS can be sent to Excel and the reports can be
generated from there?? HOWEVER For each cell that is blank, put an apostrophe in it. ' Then maybe it will accept the "blank" cell or maybe a Quote mark " "Secret Squirrel" wrote: 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 |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Data from Excel to Access Table
There are many reports and queries already set up in Access to analyze the
data. And plus the users like using Access rather than Excel. How can I fix my code to be able to import more than just one row of data? Right now I have it set up so it will import data from cell A10:O10. What about multiple rows? How would I do this? "exceluserforeman" wrote: Then maybe the stuff in ACCESS can be sent to Excel and the reports can be generated from there?? HOWEVER For each cell that is blank, put an apostrophe in it. ' Then maybe it will accept the "blank" cell or maybe a Quote mark " "Secret Squirrel" wrote: 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 |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Data from Excel to Access Table
I ma out of my depth here but ...
where it says & "Values ('" & Range("A10").Value & "', '" & Range("B10").Value & "', etc... maybe just & "Values ('" & Range("A1:O10").Value, which covers all the rows instead of layering each cell intot he table one by one. Otherwise a for loop for each cell range written to a string Maybe ask in ACCESS section - -Mark "Secret Squirrel" wrote: There are many reports and queries already set up in Access to analyze the data. And plus the users like using Access rather than Excel. How can I fix my code to be able to import more than just one row of data? Right now I have it set up so it will import data from cell A10:O10. What about multiple rows? How would I do this? "exceluserforeman" wrote: Then maybe the stuff in ACCESS can be sent to Excel and the reports can be generated from there?? HOWEVER For each cell that is blank, put an apostrophe in it. ' Then maybe it will accept the "blank" cell or maybe a Quote mark " "Secret Squirrel" wrote: 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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Can I copy a table from Word into one cell in Excel without losing data? (Office 97) | Excel Discussion (Misc queries) | |||
| Linking table in Excel to word | Links and Linking in Excel | |||
| Fetching External Data from Excel | Excel Discussion (Misc queries) | |||
| Data population between excel and access - Errors on missed fields | Excel Discussion (Misc queries) | |||
| Data Table - Does it work with DDE links and stock tickers? | Excel Discussion (Misc queries) | |||
Hybrid Mode
