Adding a Field - error in value

ca1358
ca1358 used Ask the Experts™
on
Adding Field 5 name Concatenation --Text getting error Syntax error.   This is the value I added  cell. Offset(0, 5).Value & "',")

Here all the code:
Const dbConStr As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=\\DTCHYB-ILSP001\C_MTG_Groups\Mandatory\Analysts - Working Files\Carol\Expert\LSB_analysis.accdb;Persist Security Info=False;"

Sub CopyToAccess()
Dim ws As Worksheet
Dim dbConn As Object
Dim dbCmd As Object
Dim lr As Long
Dim rng As Range, cell As Range

Set ws = Sheets("Sales_Summary")
lr = ws.Cells(Rows.Count, "BA").End(xlUp).Row
Set rng = ws.Range("BA1:BA" & lr)

Set dbConn = CreateObject("ADODB.Connection")
Set dbCmd = CreateObject("ADODB.Command")

On Error GoTo ErrHandler
dbConn.ConnectionString = dbConStr
dbConn.Open
dbCmd.ActiveConnection = dbConn
On Error GoTo 0

For Each cell In rng
   On Error GoTo DataNotInserted
   dbCmd.CommandText = "INSERT INTO Daily_Historical_Bid_Log (Trade_Date,Trade_Month,ClientID,Client,Tape_Amt,Concatenation) VALUES (" & _
                     "#" & cell.Value & "#," & _
                     "'" & cell.Offset(0, 1).Value & "'," & _
                     cell.Offset(0, 2).Value & "," & _
                     "'" & cell.Offset(0, 3).Value & "'," & _
                     cell.Offset(0, 4).Value & ")"
                     cell.Offset(0, 5).Value & "',")
   dbCmd.Execute
Next cell
MsgBox "Data has been successfully inserted into Access Table.", vbInformation, "Done!"
Exit Sub

DataNotInserted:
MsgBox "Record is not inserted." & vbNewLine & Err.Number & vbNewLine & Err.Description
dbConn.Close
ErrHandler:
MsgBox "Something went wrong, unable to connect to the Access Database. " & vbNewLine & _
       Err.Number & vbNewLine & Err.Description

End Sub

Open in new window

Copy-of-CopyExportToAccess.xlsm
LSB_analysis.accdb
ValueError.docx
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Top Expert 2014

Commented:
Please try this:
dbCmd.CommandText = "INSERT INTO Daily_Historical_Bid_Log (Trade_Date,Trade_Month,ClientID,Client,Tape_Amt,Concatenation) VALUES (" & _
                     "#" & cell.Value & "#," & _
                     "'" & cell.Offset(0, 1).Value & "'," & _
                     cell.Offset(0, 2).Value & "," & _
                     "'" & cell.Offset(0, 3).Value & "'," & _
                     cell.Offset(0, 4).Value & ")," & _
                     "'" & cell.Offset(0, 5).Value & "'")

Open in new window

Author

Commented:
It turn all RED.
Second_ValueError.docx

Author

Commented:
took the ) of this line   "'" & cell.Offset(0, 5).Value & "'") but now error read Record is not inserted. -2147217900 Number of qury values and destination fields are not the same.
Top Expert 2014
Commented:
Misplaced right parenthesis character.  Please test this:
dbCmd.CommandText = "INSERT INTO Daily_Historical_Bid_Log (Trade_Date,Trade_Month,ClientID,Client,Tape_Amt,Concatenation) VALUES (" & _
                     "#" & cell.Value & "#," & _
                     "'" & cell.Offset(0, 1).Value & "'," & _
                     cell.Offset(0, 2).Value & "," & _
                     "'" & cell.Offset(0, 3).Value & "'," & _
                     cell.Offset(0, 4).Value & "," & _
                     "'" & cell.Offset(0, 5).Value & "')")

Open in new window

Author

Commented:
Thank you!!!!!!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial