INSERT INTO fails

[MissyEd]

Hi there,

I am trying to add an entry to a table via an Insert into statement. It fails every time and I cant work out why.

My table is called tblActionTracking with structure:

Pkey: ActionNumber - Autonumber, Unique
ActionTypeID - Integer, Indexed - Not Unique
MachineNumber - Text (20). Reqd=No, Zero length = Yes
RecordCreatedOn - Date - Default Value = Now()
RecordCreatedBy - Integer
FileImported - Boolean - Default Value = False

My SQL statement is:
CurrentDb.Execute _
"INSERT INTO tblActionTracking(ActionTypeID, MachineNumber, RecordCreatedBy) VALUES (" & ActionTypeID & ",'" & MachineNumber & "', " & RecordCreatedBy & "", dbFailOnError

It is accessed as a method in a class. I did a debug.print of the data being passed to it and got this:

INSERT INTO tblActionTracking(ActionTypeID, MachineNumber, RecordCreatedBy) VALUES(1,'OEMCOMPUTER ', 122); 128

I pasted it into a qry, removing the 128 at the end and it worked perfectly.

Any ideas.

BTW I'm using Access 97. SR-2, with jet engine release 3.5 SP3.

Thanks in advance. Missy Ed
Looking to exchange ideas and tips on VB and MS Access development as well as office 97 development. Drop me a line: msedbbw@hotmail.com

 

[Cagliostro]

I've gotten the same error many times working throug ODBC interface with databases. ODBC doesn't support such operations even if database does.
try
DIM xxx as string
xxx='something'
CurrentDb.execute _
"INSERT INTO tblActionTracking(ActionTypeID, MachineNumber, RecordCreatedBy) VALUES (" & ActionTypeID & "," & xxx & ", " & RecordCreatedBy & "", dbFailOnError John Fill

ivfmd@mail.md

 

[tlbroadbent]

What indication do you have that the insert has failed? Are you able to capture an error message? With dbFailOnEror you Access should return an error message when the Insert fails.

Terry

People who don't take risks generally make about two big mistakes a year. People who do take risks generally make about two big mistakes a year. -Peter Drucker

 

[MissyEd]

Thanks Terry,

I have traced the error down to the MachineNumber string.
I have used an API to return the MachineNumber as a string as below. Despite using the Trim function, the string still appeared to have an addition space. When I tried to get the Ascii value to make sure it was a string, 0 was returned. Im not sure if this means it is actually ascii no. 0 or the function failed. I'll keep investigating!

' Error message
3075 - Syntax error in string in query expression "OEMCOMPUTER'.

' API for computer name
Public Declare Function api_GetComputerName Lib "Kernel32" Alias _
"GetComputerNameA" (ByVal lpBuffer As String, nSize As Long) As Long

' Function to retreive computername
Dim NBuffer As String
Dim RetreiveChk As Long

NBuffer = Space$(256)


If boolChoice = False Then
RetreiveChk = api_GetUserName(NBuffer, 256)
ComputerUserName = Trim$(CStr(NBuffer))
Else
RetreiveChk = api_GetComputerName(NBuffer, 256)
ComputerUserName = Trim$(CStr(NBuffer))
End If
Missy Ed
Looking to exchange ideas and tips on VB and MS Access development as well as office 97 development. Drop me a line: msedbbw@hotmail.com