anything wrong with this sql

[garethl]

strSQL = "INSERT INTO TBL_History (Filename, Agent, SentRecieved, Accepted, Notes) VALUES ('" & [Forms]!FRM_ImportSubmission.submission_filename & "','" & [Forms]![FRM_ImportSubmission]![cboAgent] & "','Recieved','True','');"

CurrentProject.Connection.Execute strSQL

i've posted it as the sql string is built in the code

i get a 'catastrpohic error' when running it from vb like this and when i build put the string into a query and run it through the access user interface it worrks

 

[Dennisk]

its a bit difficult to read, but do you have a single quote followed by a double quote, normally it is the other way round. so the single quote is on the inside of a double quote.

 

[garethl]

i think you might be right

the line i missed out of the post however was

msgbox strSQL, vbokonly, "debug"

i'm suprised i didn't notice when looking at the string in the message box but i'll have another look

 

[garethl]

no its isn't that the string evaluates to this

INSERT INTO TBL_History (Filename, Agent, SentRecieved, Accepted, Notes) VALUES ('V:\Energy_Efficiency\ENRGEFF\EEC 2\Database\Database Models\gareths_test\test_input.xls','1','Recieved','True','');

i think what you could see was probably the empty string '' which is being written into the notes field

 

[neileg]

The zero length string is usually denoted as "" (two double quotes). I don't know if '' (two single quotes) is acceptable

 

[garethl]

i actually worked out what it didn't like it was the

[Forms]!FRM_ImportSubmission.submission_filename

part

well when those two bits of data that are pulled from the form are included in the sql string like that (rather than resolved to strings first) access will cope fine with

[Forms]![FRM_ImportSubmission]![cboAgent]

but not

[Forms]!FRM_ImportSubmission.submission_filename

i should be able to work round it by pulling that file name in a different way but when i wrote in the code i got an error 'invalid argument' then it wouldn't let me save the vb

and now whenever i try and run anything i get this 'invalid argument' if i try and do a compact and repair i get 'invalid argument' basically it sounds like this has managed to corrupt the database somehow

 

[unclejoe]

Hi,

Have you tried to remove the last comma?

'Recieved','True','');"

To

'Recieved','True''');"

 

[garethl]

well it works fine like this

strSQL = "INSERT INTO TBL_History (Filename, Agent, SentRecieved, Accepted, Notes) VALUES ('some string'" & [Forms]![FRM_ImportSubmission]![cboAgent] & "','Recieved','True','');"

my problem now is that i can't access the database at all it seems this has turned into a database corruption problem

 

[unclejoe]

hi,

However, that was an incorrect SQL syntax, you'll need to remove that comma. Jet, i think is attempting to insert another blank field to somewhere into your table but it does not know where. You have declared 5 field but 4 field for inserting.

Is the last one a blank?

well it works fine like this

strSQL = "INSERT INTO TBL_History (Filename, Agent, SentRecieved, Accepted, Notes) VALUES ('some string'" & [Forms]![FRM_ImportSubmission]![cboAgent] & "','Recieved','True','');"

my problem now is that i can't access the database at all it seems this has turned into a database corruption problem

 

[garethl]

sorry there is a comma missing from that last one

the top one which didn't work

strSQL = "INSERT INTO TBL_History (Filename, Agent, SentRecieved, Accepted, Notes) VALUES ('" & [Forms]!FRM_ImportSubmission.submission_filename & "','" & [Forms]![FRM_ImportSubmission]![cboAgent] & "','Recieved','True','');"

is 5 fields and 5 fields if you count the empty string at the end

 

[unclejoe]

Hi,

You don't need the comma if you want a blank field.

If you'll observe the string concatenation
& "','Recieved','True',''

Your result is ','Recieved','True',
with the last comma showing up.

PS - Just use 4 fields only if the Column "Notes" is blank.

sorry there is a comma missing from that last one
the top one which didn't work
strSQL = "INSERT INTO TBL_History (Filename, Agent, SentRecieved, Accepted, Notes) VALUES ('" & [Forms]!FRM_ImportSubmission.submission_filename & "','" & [Forms]![FRM_ImportSubmission]![cboAgent] & "','Recieved','True','');"
is 5 fields and 5 fields if you count the empty string at the end

 

[garethl]

i solved this on in the end

the reason that

strSQL = "INSERT INTO TBL_History (Filename, Agent, SentRecieved, Accepted, Notes) VALUES ('some string'" & [Forms]![FRM_ImportSubmission]![cboAgent] & "','Recieved','True','');"

works fine is nothing to do with it trying to resolve

[Forms]!FRM_ImportSubmission.submission_filename

it is actually that the string in question (the filename) was too long!

the maximum length of the field was set to 50, setting to 255 resolves theb issue

i eventually noticed this when trying once again to paste the sql into the query builder and run like that - which as i believe i said before works fine BUT (and this is what i didn't notice before) truncates the string to 50 characters

when you run the same sql from vb code it throws the "catastrophic failiure" instead of truncating the string

 

[unclejoe]

i solved this on in the end the reason that
strSQL = "INSERT INTO TBL_History (Filename, Agent, SentRecieved, Accepted, Notes) VALUES ('some string'" & [Forms]![FRM_ImportSubmission]![cboAgent] & "','Recieved','True','');"

I don't how you can resolve the problem without removing the last comma.

With the comma, I can receive an error in Access in both the QBE and VB.

Run-Time error '3134':

Syntax error in INSERT INTO statement.

works fine is nothing to do with it trying to resolve [Forms]!FRM_ImportSubmission.submission_filename
it is actually that the string in question (the filename) was too long!
the maximum length of the field was set to 50, setting to 255 resolves theb issue
i eventually noticed this when trying once again to paste the sql into the query builder and run like that - which as i believe i said before works fine BUT (and this is what i didn't notice before) truncates the string to 50 characters

when you run the same sql from vb code it throws the "catastrophic failiure" instead of truncating the string

Have you compact and repair the database? I don't why the run-time error does not show up in yours. Perhaps,may be repairing your database might help.

 

[garethl]

yeah database has been repaired

i think you might be thinking that i have one of these "

instead of two of these '

which is what i have, but yeah your right i could just leave notes blank

so

strSQL = "INSERT INTO TBL_History (Filename, Agent, SentRecieved, Accepted) VALUES ('" & strFilename & "',[Forms]![FRM_ImportSubmission]![cboAgent],'Recieved','True');"

 

[unclejoe]

yeah database has been repaired
i think you might be thinking that i have one of these "
instead of two of these '

No, refer to previous post on the ampersand string concatenation.

which is what i have, but yeah your right i could just leave notes blank

You still can leave the “Notes” field there, but you’ll have to input a blank field like….

'Recieved','True', ‘ ‘);"

You’ll have to leave a blank space between the two single quotes for it to work.

so
strSQL = "INSERT INTO TBL_History (Filename, Agent, SentRecieved, Accepted) VALUES ('" & strFilename & "',[Forms]![FRM_ImportSubmission]![cboAgent],'Recieved','True');"

Hopefully is a typo. You miss the ampersand, double and single quotes.

strSQL = "INSERT INTO TBL_History (Filename, Agent, SentRecieved, Accepted) VALUES ('" & strFilename & "'," & [Forms]![FRM_ImportSubmission]![cboAgent] & “’,'Recieved','True');"

PS. You can leave out the semi-colon in VB.

 

[garethl]

right i see what you mean now, no i don't have a space in it in the code so its weird that it works i'll ahve another look at it