Member Avatar for Mike Bishop

I have the following code

Dim adoCN As ADODB.Connection
Dim sConnString As String
Dim sSQL As String
Dim lRow As Long, lCol As Long

sConnString = "Provider=sqloledb;Server=MonsterSQ1;Database=AmiCatSQL;User Id=SA;Password=123456"

Set adoCN = CreateObject("ADODB.Connection")

adoCN.Open sConnString

'Assumes that you have Field1, Field2 and Field3 in columns A, B and C
'For this example we can assume that the data exists on Sheet1, with a header on row
'1 and data in rows 2-11
'Also assume that the fields are defined as character (e.g. varchar or char)
'Text values must be enclosed in apostrophes whereas numeric values should not.

For lRow = 2 To 4

sSQL = "INSERT INTO KMB (ItemNo,ItemQuoteNo,GFlag) " & " VALUES (" & "'" & Parameters.Cells(lRow, 1) & "',  " & "'" & Parameters.Cells(lRow, 3) & "')"


adoCN.Execute sSQL

Next lRow

adoCN.Close

Set adoCN = Nothing

But when running it i get run-time error 424 object required. I am running this in excel VBA

if someone could help that would be great.

  • 3 Contributors
  • 2 Replies
  • 124 Views
  • 18 Hours Discussion Span
  • Latest Post Latest Post by QVeen72
Member Avatar for AndreRet 
sSQL = "INSERT INTO KMB (ItemNo, ItemQuoteNo, GFlag) VALUES ("'" & Parameters.Cells(lRow, 1) & "',  '" & Parameters.Cells(lRow, 3) & "')"

You need to put a reference to the data object. This can be done, depending on your version of excell - get your solution HERE from microsoft.

Member Avatar for QVeen72

Hi,

Your SQL String, shows only 2 values...
You need to Insert 3 values.....
If u get confused, write Debug.Print sSQL
and check in the immediate/debug window...

Regards
Veena

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.