ADODB ACCESS MDB Problem

[bsharp]

I am working on a script for a program that uses an access database. The MDB table I am working with consists of 1 row and 200 columns every 8 columns consists of a relative group. I successfully imported the data using a delimited string and than used separate to import them. Hence I only needed 25 separate functions to import the data. I then collated the data and built a dialog box to manipulate it with. Now I have to set the data back to the MDB. But I can’t seem to be able to set the table field up as a variable. It would be very helpful if I could do this as writing 200 separate VB functions for each import would be crazy.


This is my test script;

VBSTART
Sub ChangePROG(SIQ,NewPROG)

set MyDB = CreateObject("ADODB.Connection")
MyDB.Open "FMS"
MyDB.Execute = "Update SCHE Set SIQ = ('" & NewPROG & "')"
MyDB.Close

End Sub
VBEND
LET>SIQ=
LET>SIQCNT=0
LET>PROGRAM_SIQ=PROGRAM_SIQ%CRLF%
LABEL>MODLOOP
ADD>SIQCNT,1
LET>SIQ=PROGRAM_SIQ%SIQCNT%
Input>NewPROG,Enter New PROG:,TEST
VBRun>ChangePROG,SIQ,NewPROG
Goto>MODLOOP


If I change SIQ in the VB to PROGRAM_SIQ1 it will work but I need it to pass a variable.
I am sure there must be a simple way of doing it.

[Marcus Tettmar]

Hi,

SIQ is a literal in your SQL string:

MyDB.Execute = "Update SCHE Set SIQ = ('" & NewPROG & "')"

I think you mean:

MyDB.Execute = "Update SCHE Set " & SIQ & " = ('" & NewPROG & "')"

However, I notice that you have embedded a CRLF (carriage return and line feed) character pair in the variable. This is likely to cause errors since I doubt SQL particularly likes field names with line breaks in them.

[bsharp]

Thank You it works like a charm. I had thought I had tried that but I must have missed some thing. I knew there was a simple way.