VB6 ADO INSERT of TEXT field into .MDB

Question

My example VB6 program:

Dim conn As New connection
conn.Open "Driver={Microsoft Access Driver (*.mdb)};Dbq=" & App.Path & "\adatabase.mdb;"
conn.Execute "INSERT INTO atable (afield) VALUES (""some text"")"
End Sub

("adatabase.mdb" contains one table "atable" with one field "afield" of type "Text" length 255. Access 2002, Access file format 2000. VB6 references include "Microsoft ActiveX Data Objects 2.8 Library".)

conn.Execute gets:

Runtime error '-2147217904 (80040e10' [Microsoft][ODBC Access Driver] Too few parameters. Expected 1.

The query

INSERT INTO atable (afield) VALUES ("some text")

runs directly in Access without any problem.

You're still writing VB6?! "The final release was version 6 in 1998. Microsoft's extended support ended in March 2008 and the designated successor was Visual Basic .NET (now known simply as Visual Basic)." — Lightness Races in Orbit
Thanks Tomalak. Not news. Naturally I have reasons which I won't go into here. — Steve Rogers

Mitch Wheat Mitch Wheat · Accepted Answer · 2011-08-14T01:48:03

Shouldn't that be:

conn.Execute "INSERT INTO atable (afield) VALUES ("""some text""")"

i.e. you need to quote the double quotes?

Update: or as the poster solved, use single quotes to quote text.

Or even better, use a parameterised query:

Dim Conn1 As ADODB.Connection
Dim Cmd1 As ADODB.Command
Dim Param1 As ADODB.Parameter
Dim Rs1 As ADODB.Recordset

' Create and Open Connection Object.
Set Conn1 = New ADODB.Connection
Conn1.ConnectionString = "DSN=Biblio;UID=admin;PWD=;"
Conn1.Open

' Create Command Object.
Set Cmd1 = New ADODB.Command
Cmd1.ActiveConnection = Conn1
Cmd1.CommandText = "SELECT * FROM Authors WHERE AU_ID < ?"

' Create Parameter Object.
Set Param1 = Cmd1.CreateParameter(, adInteger, adParamInput, 5)
Param1.Value = 5
Cmd1.Parameters.Append Param1
Set Param1 = Nothing

' Open Recordset Object.
Set Rs1 = Cmd1.Execute()

VB6 ADO INSERT of TEXT field into .MDB

1 Answers