problem with VBA updating access file

[Anonymous]

hello fellas, could anyone please point out whats wrong with my SQL string? when i run the RSinfo line i get an invalid arguement error.



SQLQuery = "UPDATE tblcompleteupdatetable " & _
"SET tblcompleteupdatetable.lastweldno = " & WeldNumber & " " & _
"WHERE tblcompleteupdatetable.linenumber = '" & DwgLineNumber & _
"' AND tblcompleteupdatetable.workpacknumber = '" & WorkPackNumber & "' ;"


ActiveDB = "P:\Plant_Projects\PlantProjects.mdb"
Set DBinfo = OpenDatabase(ActiveDB) 'Open database
Set RSinfo = Nothing

Set AllTables = DBinfo.TableDefs

For Each oTable In AllTables
If LCase(oTable.Name) Like "tblcompleteupdatetable" = True Then

Set RSinfo = DBinfo.OpenRecordset(SQLQuery, dbOpenDynaset)

[Anonymous]

Hi acadape,

Are you sure your OpenDataBase function is working?
Are your field definitions for DwgLineNumber and DwgLineNumber numeric?
If so you need not enclose the values with the single quote markers.

Try using debug.print with the SQLQuery string and then try and run the
output in Access to find any errors in your assembly of the string.

Regards,


Laurie Comerford


acadape wrote:
> hello fellas, could anyone please point out whats wrong with my SQL string? when i run the RSinfo line i get an invalid arguement error.
>
>
>
> SQLQuery = "UPDATE tblcompleteupdatetable " & _
> "SET tblcompleteupdatetable.lastweldno = " & WeldNumber & " " & _
> "WHERE tblcompleteupdatetable.linenumber = '" & DwgLineNumber & _
> "' AND tblcompleteupdatetable.workpacknumber = '" & DwgLineNumber & "' ;"
>
>
> ActiveDB = "P:\Plant_Projects\PlantProjects.mdb"
> Set DBinfo = OpenDatabase(ActiveDB) 'Open database
> Set RSinfo = Nothing
>
> Set AllTables = DBinfo.TableDefs
>
> For Each oTable In AllTables
> If LCase(oTable.Name) Like "tblcompleteupdatetable" = True Then
>
> Set RSinfo = DBinfo.OpenRecordset(SQLQuery, dbOpenDynaset)

[Anonymous]

Hi Laurie, thank you for replying. The database connection is ok, i cant import data from the database into the attribute fields of my block.
unfortunately i cant get my function to update from the attributes into the database.

the debug message is

Error number 3219 has occured because: Invalid operation

the sql query is this:

UPDATE tblcompleteupdatetable SET tblcompleteupdatetable.lastweldno = '7' WHERE tblcompleteupdatetable.linenumber = 'IA100208-S01' AND tblcompleteupdatetable.workpacknumber = '14' ;

these field exist, i just cant update them so far.

i could sent my whole dvb file if it would help

regards

[Anonymous]

You cannot open a recordset (RSinfo) on a "UPDATE..." SQL statement., You
open Recordset against either a table, or a query, or a "SELECT..." SQL
statement.

In your case, you want to update data ack to database. There is no need to
open a Recordset just to send that little amount of data back. You call
Database.Execute("UPDATE..."). That is:

DBinfo.Execute SQLQuery

Of course you have to make sure your "UPDATE..." statement is correct. Hind:
according to your this post, "lastweldno" seems a numeric type, while
according to your next post, it became text type. So, make sure what type
the value should be, then you can decide if the value has to be placed
between '...' or not.

"acadape" wrote in message news:6268345@discussion.autodesk.com...
hello fellas, could anyone please point out whats wrong with my SQL string?
when i run the RSinfo line i get an invalid arguement error.



SQLQuery = "UPDATE tblcompleteupdatetable " & _
"SET tblcompleteupdatetable.lastweldno = " & WeldNumber & " " & _
"WHERE tblcompleteupdatetable.linenumber = '" & DwgLineNumber & _
"' AND tblcompleteupdatetable.workpacknumber = '" & WorkPackNumber & "' ;"


ActiveDB = "P:\Plant_Projects\PlantProjects.mdb"
Set DBinfo = OpenDatabase(ActiveDB) 'Open database
Set RSinfo = Nothing

Set AllTables = DBinfo.TableDefs

For Each oTable In AllTables
If LCase(oTable.Name) Like "tblcompleteupdatetable" = True Then

Set RSinfo = DBinfo.OpenRecordset(SQLQuery, dbOpenDynaset)

[Anonymous]

Any time I have to do this I create the SQL string in access first. That
way you know *for sure* the syntax required.

[Anonymous]

Thank you very much for helping me here, much appreciated.

DBInfo.execute SQLQuery worked fine.

im up and running now!