We help IT Professionals succeed at work.
Get Started

Adding a Field - error in value

51 Views
Last Modified: 2016-03-30
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
Social distance; Wear a mask; Don't touch your face; Wash your hands for 20 seconds
CERTIFIED EXPERT
Top Expert 2014
Commented:
This problem has been solved!
Unlock 1 Answer and 5 Comments.
See Answer
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant

An Experts Exchange subscription includes unlimited access to online courses.

Get Started
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE