Re: Access VBA update recordset error
From: Victor Koch ("Victor)
Date: 10/29/04
- Next message: Val Mazur: "Re: AddNew Fails when too many fields are added"
- Previous message: cityofgp: "Re: Access VBA update recordset error"
- In reply to: cityofgp: "Re: Access VBA update recordset error"
- Next in thread: Val Mazur: "Re: Access VBA update recordset error"
- Messages sorted by: [ date ] [ thread ]
Date: Fri, 29 Oct 2004 15:27:22 -0300
Hi cityofgp,
-- Víctor Koch. BUG: "Syntax Error in INSERT INTO..." on ADO Recordset.UpdatePSS ID Number: 189220 Article Last Modified on 5/22/2003 ---------------------------------------------------------------------------- ---- The information in this article applies to: Microsoft Data Access Components 1.5 Microsoft Data Access Components 2.0 Microsoft Data Access Components 2.1 SP2 Microsoft Data Access Components 2.5 Microsoft Data Access Components 2.6 Microsoft Data Access Components 2.7 Microsoft ODBC Driver for Access 3.5 Microsoft ODBC Driver for Access 4.0 ---------------------------------------------------------------------------- ---- This article was previously published under Q189220 SYMPTOMS When you call the AddNew method, and then call the Update method of a Recordset, the following error is returned: Microsoft OLE DB Provider for ODBC Drivers error '80040e21' [Microsoft][ODBC Microsoft Access 97 Driver] Syntax error in INSERT INTO statement. Using the Microsoft Access ODBC Driver version4.00.4202.00 or later, the error message is: Run-time error '-2147467259(80004005)': [Microsoft][ODBC Microsoft Access Driver]Syntax error in INSERT INTO statement. CAUSE This error occurs if the following are true: The CursorType is adOpenForwardOnly (0). The CursorLocation is adUseServer. A field being updated has a space in the field name. You are using the Microsoft Access ODBC driver. Calling the AddNew and Update methods, on a Server-Side, Forward-Only cursor, is an invalid operation. ActiveX Data Objects (ADO) compensates for this code flaw by generating an INSERT statement to perform the operation. When ADO generates the INSERT statement, it does not delimit the field names. Therefore, field names that contain a space generate a syntax error. For example, a valid INSERT statement would be as follows: INSERT INTO Table ([field with space]) Values(value) ADO generates the following code: INSERT INTO Table (field with space) Values(value) RESOLUTION Here are several possible resolutions: Do not use spaces in the field names in your database. Use a CursorLocation of adUseClient (3). Do not use a CursorType of adOpenForwardOnly (0), such as adOpenKeyset (1), adOpenDynamic (2), or adOpenStatic (3). Perform the operation by generating your own INSERT statement, with delimiters on the field names, instead of using the AddNew and Update methods. Use the Microsoft OLE DB Provider for Jet version 3.51 or 4.0 instead of the Microsoft OLE DB Provider for ODBC and the Microsoft Access ODBC Driver. STATUS Microsoft has confirmed this to be a bug in the Microsoft products listed at the beginning of this article. Keywords: kbbug kbDatabase kbDriver kbJET KB189220 Technology: kbAccessSearch kbAudDeveloper kbMDAC150 kbMDAC200 kbMDAC210SP2 kbMDAC250 kbMDAC260 kbMDAC270 kbMDACSearch kbODBCAccess350 kbODBCAccess400 kbODBCSearch "cityofgp" <cityofgp@discussions.microsoft.com> escribió en el mensaje news:40FEC14F-4197-444E-8B40-DDEB99E2AF5C@microsoft.com... > This is what the exact error message says: > Runtime error '-2147467259 (80004005)': > > [Microsoft][ODBC Microsoft Access Driver] Syntax error in INSERT INTO > statement. > > And when I click on debug and takes me to the .update statement. > > > > "Brendan Reynolds" wrote: > > > I still can't see the problem. The spaces in the field names should not, as > > far as I can see, be a problem as long as you surround them with square > > brackets, as you are doing. But here's something that might help to narrow > > down the problem - try aliasing those field names in a SQL statement, > > something like so ... > > > > Dim strSQL As String > > .... > > strSQL = "SELECT Roll, ReferenceRoll, LOCCD, BLDNO, UNIT, TENANT, > > BusinessAddress, [Bus PC] AS BusPC etc. FROM tblbrzaccounts" > > .... > > ..Open strSQL, dbconn, , adLockOptimistic > > .... > > !BusPC = txtPC > > .... > > > > Also, it might help to know the exact text of the error message. > > > > -- > > Brendan Reynolds (MVP) > > http://brenreyn.blogspot.com > > > > The spammers and script-kiddies have succeeded in making it impossible for > > me to use a real e-mail address in public newsgroups. E-mail replies to > > this post will be deleted without being read. Any e-mail claiming to be > > from brenreyn at indigo dot ie that is not digitally signed by me with a > > GlobalSign digital certificate is a forgery and should be deleted without > > being read. Follow-up questions should in general be posted to the > > newsgroup, but if you have a good reason to send me e-mail, you'll find > > a useable e-mail address at the URL above. > > > > > > "cityofgp" <cityofgp@discussions.microsoft.com> wrote in message > > news:60080A55-354E-482D-ADB0-DE901E87ED2F@microsoft.com... > > > Here is the code that executes on the 'onclick' event. > > > You will notice I have commented the fieldnames that have spaces because > > > when I uncomment, I get the error message. > > > > > > Dim rs As ADODB.Recordset > > > Dim dbconn As ADODB.Connection > > > Dim rate As Integer > > > Dim amount As Integer > > > Set dbconn = New ADODB.Connection > > > dbconn.ConnectionString = "DSN=BRZ;" > > > dbconn.Open > > > > > > Set rs = New ADODB.Recordset > > > With rs > > > .Open "tblbrzaccounts", dbconn, , adLockOptimistic > > > .AddNew > > > !ROLL = txtRoll > > > !ReferenceRoll = txtRollRef > > > !LOCCD = txtLOCCD > > > !BLDNO = txtBLDNO > > > !UNIT = UNIT > > > !TENANT = txtTenant > > > ![BusinessAddress] = txtAddress > > > '![Bus PC] = txtPC > > > !TenantName = txtTenantName > > > !BusinessName = txtBusName > > > !MailingAddress1 = txtMailAdd1 > > > !MailingAddress2 = txtMailAdd2 > > > !City = txtCity > > > !Prov = txtProv > > > !MailingPC = txtBillPC > > > !Country = txtBillCountry > > > !BUSPHONE = txtWork > > > !BusinessRepName = txtBusRepName > > > !LessorsName = txtLessor > > > !LSSRPHONE = txtPhone > > > !Comments = txtComments > > > !CHANGESystem = txtChanges > > > !Start = txtStart > > > !End = txtEnd > > > '![Tentan TypeCode] = txtTenTypeCode > > > !Size = txtSize > > > !AssessmentCode = txtAssessmentCode > > > !BusinessType = txtBusType > > > '![Tax Status] = txtTaxStatus > > > '![Millrate Code] = txtMillrateCode > > > '![Current Assmt] = txtCurrentAssmt > > > ![2003TaxPrevAsmt] = txt2003PrevAssmt > > > !Status = txtStatus > > > '![Drive Code] = CStr(txtLOCCD & "-" & txtBLDNO & txtUnit & > > > txtTenant) > > > .Update > > > End With > > > rs.Close > > > dbconn.Close > > > MsgBox "saved" > > > > > > "Brendan Reynolds" wrote: > > > > > >> I think we'll need to see the actual code to trouble-shoot this one. The > > >> full text of the error message might help, too. > > >> > > >> -- > > >> Brendan Reynolds (MVP) > > >> http://brenreyn.blogspot.com > > >> > > >> The spammers and script-kiddies have succeeded in making it impossible > > >> for > > >> me to use a real e-mail address in public newsgroups. E-mail replies to > > >> this post will be deleted without being read. Any e-mail claiming to be > > >> from brenreyn at indigo dot ie that is not digitally signed by me with a > > >> GlobalSign digital certificate is a forgery and should be deleted without > > >> being read. Follow-up questions should in general be posted to the > > >> newsgroup, but if you have a good reason to send me e-mail, you'll find > > >> a useable e-mail address at the URL above. > > > > > >
- Next message: Val Mazur: "Re: AddNew Fails when too many fields are added"
- Previous message: cityofgp: "Re: Access VBA update recordset error"
- In reply to: cityofgp: "Re: Access VBA update recordset error"
- Next in thread: Val Mazur: "Re: Access VBA update recordset error"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
|
