Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
ADO SQL statment
I'm trying to modify the code below. I have an Excel sheet with Journal
Voucher data that I want to add to a table in an Access Database. Each line in the spreadsheet will be a new record in the Access table. The Journal Voucher data has 12 fields, (Cust Num, Doc, Unit, DR Acct, DR BR, DR Amt, Cr Acct, CR BR, CR Amt, Descr, Vend Num, Date, Time). I have 3 questions: (1) What is the correct syntax to add a new record to Access with mupltiple fields (having trouble with the correct syntax)? (2) Is there a way to do all of the lines at once instead of one at a time? (3) Is this the way to go or is it better to start an Access application and use the acimport command? Dim Recordset As ADODB.Recordset Set Recordset = New ADODB.Recordset vDataBase = ActiveWorkbook.Sheets("Macros").Range("E6") ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source= '" & vDataBase & "';Persist Security Info=False" Dim r As Integer r = 5 Do Until Len(Cells(r, 1)) = 0 Sql = "append [JV_Info] set [Customer Number] = '" & Cells(r, 1) & "' Call Recordset.Open(Sql, ConnectionString, adOpenForwardOnly, adLockReadOnly, CommandTypeEnum.adCmdText) r = r + 1 Loop Thanks for all of the help......Any suggestions or code on how to accomplish this would be greatly appreciated. -- JT |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
ADO SQL statment
Have you ever try the Access wizard to import data from excel
sheets... maybe it works for you! On Feb 6, 1:04 pm, JT wrote: I'm trying to modify the code below. I have an Excel sheet with Journal Voucher data that I want to add to a table in an Access Database. Each line in the spreadsheet will be a new record in the Access table. The Journal Voucher data has 12 fields, (Cust Num, Doc, Unit, DR Acct, DR BR, DR Amt, Cr Acct, CR BR, CR Amt, Descr, Vend Num, Date, Time). I have 3 questions: (1) What is the correct syntax to add a new record to Access with mupltiple fields (having trouble with the correct syntax)? (2) Is there a way to do all of the lines at once instead of one at a time? (3) Is this the way to go or is it better to start an Access application and use the acimport command? Dim Recordset As ADODB.Recordset Set Recordset = New ADODB.Recordset vDataBase = ActiveWorkbook.Sheets("Macros").Range("E6") ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source= '" & vDataBase & "';Persist Security Info=False" Dim r As Integer r = 5 Do Until Len(Cells(r, 1)) = 0 Sql = "append [JV_Info] set [Customer Number] = '" & Cells(r, 1) & "' Call Recordset.Open(Sql, ConnectionString, adOpenForwardOnly, adLockReadOnly, CommandTypeEnum.adCmdText) r = r + 1 Loop Thanks for all of the help......Any suggestions or code on how to accomplish this would be greatly appreciated. -- JT |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
ADO SQL statment
Inserting a row statement is like this
sSQL = "INSERT INTO Contacts " & _ " (FirstName, LastName,Phone, Notes) " & _ "VALUES ('Bob','Phillips','01202 345678','me')" AFAIAA, you can't insert multiples at once into Access. -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "JT" wrote in message ... I'm trying to modify the code below. I have an Excel sheet with Journal Voucher data that I want to add to a table in an Access Database. Each line in the spreadsheet will be a new record in the Access table. The Journal Voucher data has 12 fields, (Cust Num, Doc, Unit, DR Acct, DR BR, DR Amt, Cr Acct, CR BR, CR Amt, Descr, Vend Num, Date, Time). I have 3 questions: (1) What is the correct syntax to add a new record to Access with mupltiple fields (having trouble with the correct syntax)? (2) Is there a way to do all of the lines at once instead of one at a time? (3) Is this the way to go or is it better to start an Access application and use the acimport command? Dim Recordset As ADODB.Recordset Set Recordset = New ADODB.Recordset vDataBase = ActiveWorkbook.Sheets("Macros").Range("E6") ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source= '" & vDataBase & "';Persist Security Info=False" Dim r As Integer r = 5 Do Until Len(Cells(r, 1)) = 0 Sql = "append [JV_Info] set [Customer Number] = '" & Cells(r, 1) & "' Call Recordset.Open(Sql, ConnectionString, adOpenForwardOnly, adLockReadOnly, CommandTypeEnum.adCmdText) r = r + 1 Loop Thanks for all of the help......Any suggestions or code on how to accomplish this would be greatly appreciated. -- JT |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
ADO SQL statment
Bob,
Not tested in Access, but with SQL you can get an empty DISCONNECTED recordset (SELECT TOP 0 FROM MyTable), add your records to the rs, reconnect, then do an UpdateBatch. Robin Hammond www.enhanceddatasystems.com "Bob Phillips" wrote in message ... Inserting a row statement is like this sSQL = "INSERT INTO Contacts " & _ " (FirstName, LastName,Phone, Notes) " & _ "VALUES ('Bob','Phillips','01202 345678','me')" AFAIAA, you can't insert multiples at once into Access. -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "JT" wrote in message ... I'm trying to modify the code below. I have an Excel sheet with Journal Voucher data that I want to add to a table in an Access Database. Each line in the spreadsheet will be a new record in the Access table. The Journal Voucher data has 12 fields, (Cust Num, Doc, Unit, DR Acct, DR BR, DR Amt, Cr Acct, CR BR, CR Amt, Descr, Vend Num, Date, Time). I have 3 questions: (1) What is the correct syntax to add a new record to Access with mupltiple fields (having trouble with the correct syntax)? (2) Is there a way to do all of the lines at once instead of one at a time? (3) Is this the way to go or is it better to start an Access application and use the acimport command? Dim Recordset As ADODB.Recordset Set Recordset = New ADODB.Recordset vDataBase = ActiveWorkbook.Sheets("Macros").Range("E6") ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source= '" & vDataBase & "';Persist Security Info=False" Dim r As Integer r = 5 Do Until Len(Cells(r, 1)) = 0 Sql = "append [JV_Info] set [Customer Number] = '" & Cells(r, 1) & "' Call Recordset.Open(Sql, ConnectionString, adOpenForwardOnly, adLockReadOnly, CommandTypeEnum.adCmdText) r = r + 1 Loop Thanks for all of the help......Any suggestions or code on how to accomplish this would be greatly appreciated. -- JT |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
ADO SQL statment
Hi Robin,
That is so, but I don't think that Access has that capability, or any such capability. -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Robin Hammond" wrote in message ... Bob, Not tested in Access, but with SQL you can get an empty DISCONNECTED recordset (SELECT TOP 0 FROM MyTable), add your records to the rs, reconnect, then do an UpdateBatch. Robin Hammond www.enhanceddatasystems.com "Bob Phillips" wrote in message ... Inserting a row statement is like this sSQL = "INSERT INTO Contacts " & _ " (FirstName, LastName,Phone, Notes) " & _ "VALUES ('Bob','Phillips','01202 345678','me')" AFAIAA, you can't insert multiples at once into Access. -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "JT" wrote in message ... I'm trying to modify the code below. I have an Excel sheet with Journal Voucher data that I want to add to a table in an Access Database. Each line in the spreadsheet will be a new record in the Access table. The Journal Voucher data has 12 fields, (Cust Num, Doc, Unit, DR Acct, DR BR, DR Amt, Cr Acct, CR BR, CR Amt, Descr, Vend Num, Date, Time). I have 3 questions: (1) What is the correct syntax to add a new record to Access with mupltiple fields (having trouble with the correct syntax)? (2) Is there a way to do all of the lines at once instead of one at a time? (3) Is this the way to go or is it better to start an Access application and use the acimport command? Dim Recordset As ADODB.Recordset Set Recordset = New ADODB.Recordset vDataBase = ActiveWorkbook.Sheets("Macros").Range("E6") ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source= '" & vDataBase & "';Persist Security Info=False" Dim r As Integer r = 5 Do Until Len(Cells(r, 1)) = 0 Sql = "append [JV_Info] set [Customer Number] = '" & Cells(r, 1) & "' Call Recordset.Open(Sql, ConnectionString, adOpenForwardOnly, adLockReadOnly, CommandTypeEnum.adCmdText) r = r + 1 Loop Thanks for all of the help......Any suggestions or code on how to accomplish this would be greatly appreciated. -- JT |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
IF Statment | Excel Discussion (Misc queries) | |||
IF Statment | Excel Worksheet Functions | |||
Help with If statment | Excel Discussion (Misc queries) | |||
If statment if its possible. | Excel Discussion (Misc queries) | |||
If statment | Excel Worksheet Functions |