cannot update fields with spaces in fieldnames

[cityofgp]

Using the recordset object, and a connetion object, I can connect to a
database and add records, howevere where the fieldname has spaces, I am
unable to update.
Following is the code I am using in the onclick event
You will notice I have commented out the field names that have spaces as
that generates a syntax error...
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.
Is there a way I can update those columns that have spaces in the fieldnames?

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"

 

[Douglas J. Steele]

See whether

.Fields("Bus PC") = txtPC

works.

On the other hand, if you have the ability to change the field names, I'd
strongly recommend that. Use of a third part product to ensure you catch all
references to the old field names would probably be advisable:

Find & Replace: http://www.rickworld.com/products.html
Speed Ferret: http://www.moshannon.com/speedferret.html
Ucora's: http://www3.bc.sympatico.ca/starthere/findandreplace/

 

[cityofgp]

I got the same error message:
[Microsoft][ODBC Microsoft Access Driver] Syntax error in INSERT INTO
statement

when I click on debug it highlights the .update statement

Any ideas??
Any way I could see what the actual SQL statement gets generated?

See whether

.Fields("Bus PC") = txtPC

works.

On the other hand, if you have the ability to change the field names, I'd
strongly recommend that. Use of a third part product to ensure you catch all
references to the old field names would probably be advisable:

Find & Replace: http://www.rickworld.com/products.html
Speed Ferret: http://www.moshannon.com/speedferret.html
Ucora's: http://www3.bc.sympatico.ca/starthere/findandreplace/


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Using the recordset object, and a connetion object, I can connect to a
database and add records, howevere where the fieldname has spaces, I am
unable to update.
Following is the code I am using in the onclick event
You will notice I have commented out the field names that have spaces as
that generates a syntax error...
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.
Is there a way I can update those columns that have spaces in the fieldnames?

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"

 

[Randy Harris]

The syntax that Doug supplied will work with a Jet database. What kind of
database is on the other end of that connection?

I got the same error message:
[Microsoft][ODBC Microsoft Access Driver] Syntax error in INSERT INTO
statement

when I click on debug it highlights the .update statement

Any ideas??
Any way I could see what the actual SQL statement gets generated?

See whether

.Fields("Bus PC") = txtPC

works.

On the other hand, if you have the ability to change the field names, I'd
strongly recommend that. Use of a third part product to ensure you catch all
references to the old field names would probably be advisable:

Find & Replace: http://www.rickworld.com/products.html
Speed Ferret: http://www.moshannon.com/speedferret.html
Ucora's: http://www3.bc.sympatico.ca/starthere/findandreplace/


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Using the recordset object, and a connetion object, I can connect to a
database and add records, howevere where the fieldname has spaces, I am
unable to update.
Following is the code I am using in the onclick event
You will notice I have commented out the field names that have spaces as
that generates a syntax error...
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.
Is there a way I can update those columns that have spaces in the fieldnames?

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"

 

[cityofgp]

Its an access2000 database. Basically its the same database that Im running
the code on.

The syntax that Doug supplied will work with a Jet database. What kind of
database is on the other end of that connection?

I got the same error message:
[Microsoft][ODBC Microsoft Access Driver] Syntax error in INSERT INTO
statement

when I click on debug it highlights the .update statement

Any ideas??
Any way I could see what the actual SQL statement gets generated?

See whether

.Fields("Bus PC") = txtPC

works.

On the other hand, if you have the ability to change the field names, I'd
strongly recommend that. Use of a third part product to ensure you catch all
references to the old field names would probably be advisable:

Find & Replace: http://www.rickworld.com/products.html
Speed Ferret: http://www.moshannon.com/speedferret.html
Ucora's: http://www3.bc.sympatico.ca/starthere/findandreplace/


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



Using the recordset object, and a connetion object, I can connect to a
database and add records, howevere where the fieldname has spaces, I am
unable to update.
Following is the code I am using in the onclick event
You will notice I have commented out the field names that have spaces as
that generates a syntax error...
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.
Is there a way I can update those columns that have spaces in the
fieldnames?

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"

 

[Douglas J. Steele]

I don't understand. The code you posted didn't use an INSERT INTO statement:
it had a recordset based on a table, and was using .AddNew. Have you changed
your code since your original question?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

I got the same error message:
[Microsoft][ODBC Microsoft Access Driver] Syntax error in INSERT INTO
statement

when I click on debug it highlights the .update statement

Any ideas??
Any way I could see what the actual SQL statement gets generated?

See whether

.Fields("Bus PC") = txtPC

works.

On the other hand, if you have the ability to change the field names, I'd
strongly recommend that. Use of a third part product to ensure you catch all
references to the old field names would probably be advisable:

Find & Replace: http://www.rickworld.com/products.html
Speed Ferret: http://www.moshannon.com/speedferret.html
Ucora's: http://www3.bc.sympatico.ca/starthere/findandreplace/


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Using the recordset object, and a connetion object, I can connect to a
database and add records, howevere where the fieldname has spaces, I am
unable to update.
Following is the code I am using in the onclick event
You will notice I have commented out the field names that have spaces as
that generates a syntax error...
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.
Is there a way I can update those columns that have spaces in the fieldnames?

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"

 

[cityofgp]

Nope, the code is exactly the same. ADO generates the SQL statement to
perform this operation. And the SQL statement that ADO is generating is not
putting in the field name placeholders hence the error message....

I don't understand. The code you posted didn't use an INSERT INTO statement:
it had a recordset based on a table, and was using .AddNew. Have you changed
your code since your original question?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

I got the same error message:
[Microsoft][ODBC Microsoft Access Driver] Syntax error in INSERT INTO
statement

when I click on debug it highlights the .update statement

Any ideas??
Any way I could see what the actual SQL statement gets generated?

See whether

.Fields("Bus PC") = txtPC

works.

On the other hand, if you have the ability to change the field names, I'd
strongly recommend that. Use of a third part product to ensure you catch all
references to the old field names would probably be advisable:

Find & Replace: http://www.rickworld.com/products.html
Speed Ferret: http://www.moshannon.com/speedferret.html
Ucora's: http://www3.bc.sympatico.ca/starthere/findandreplace/


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



Using the recordset object, and a connetion object, I can connect to a
database and add records, howevere where the fieldname has spaces, I am
unable to update.
Following is the code I am using in the onclick event
You will notice I have commented out the field names that have spaces as
that generates a syntax error...
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.
Is there a way I can update those columns that have spaces in the
fieldnames?

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"

 

[Randy Harris]

What do you mean "Basically its the same database that Im running the code
on."? Is it the same database? Why are you using ODBC?

Its an access2000 database. Basically its the same database that Im running
the code on.

The syntax that Doug supplied will work with a Jet database. What kind of
database is on the other end of that connection?

I got the same error message:
[Microsoft][ODBC Microsoft Access Driver] Syntax error in INSERT INTO
statement

when I click on debug it highlights the .update statement

Any ideas??
Any way I could see what the actual SQL statement gets generated?

:

See whether

.Fields("Bus PC") = txtPC

works.

On the other hand, if you have the ability to change the field

names,
I'd

strongly recommend that. Use of a third part product to ensure you

catch
all

references to the old field names would probably be advisable:

Find & Replace: http://www.rickworld.com/products.html
Speed Ferret: http://www.moshannon.com/speedferret.html
Ucora's: http://www3.bc.sympatico.ca/starthere/findandreplace/


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



Using the recordset object, and a connetion object, I can connect to a
database and add records, howevere where the fieldname has spaces,

I
am

unable to update.
Following is the code I am using in the onclick event
You will notice I have commented out the field names that have

spaces
as

that generates a syntax error...
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.
Is there a way I can update those columns that have spaces in the
fieldnames?

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"

 

[Douglas J. Steele]

AFAIK, ADO does not generate an SQL statement when you're using a recordset
to update.

However, looking at answers given elsewhere in this thread, I don't
understand what you're trying to do at all. Why are you using something like
this to update data in the database where the code is running? You don't use
DSNs in that situation.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Nope, the code is exactly the same. ADO generates the SQL statement to
perform this operation. And the SQL statement that ADO is generating is not
putting in the field name placeholders hence the error message....

I don't understand. The code you posted didn't use an INSERT INTO statement:
it had a recordset based on a table, and was using .AddNew. Have you changed
your code since your original question?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

I got the same error message:
[Microsoft][ODBC Microsoft Access Driver] Syntax error in INSERT INTO
statement

when I click on debug it highlights the .update statement

Any ideas??
Any way I could see what the actual SQL statement gets generated?

:

See whether

.Fields("Bus PC") = txtPC

works.

On the other hand, if you have the ability to change the field

names,
I'd

strongly recommend that. Use of a third part product to ensure you

catch
all

references to the old field names would probably be advisable:

Find & Replace: http://www.rickworld.com/products.html
Speed Ferret: http://www.moshannon.com/speedferret.html
Ucora's: http://www3.bc.sympatico.ca/starthere/findandreplace/


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



Using the recordset object, and a connetion object, I can connect to a
database and add records, howevere where the fieldname has spaces,

I
am

unable to update.
Following is the code I am using in the onclick event
You will notice I have commented out the field names that have

spaces
as

that generates a syntax error...
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.
Is there a way I can update those columns that have spaces in the
fieldnames?

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"

 

[david epsom dot com dot au]

It's an interesting example, though strange. Obviously,
ADO /will/ generate SQL when talking to an ODBC driver,
because that is what the ODBC specification requires.
ADO - ADO ODBC provider - ODBC API - Jet ODBC dll - Jet.

Also note that 'update' is a relative term: he's using
..addnew, so the generated ODBC SQL is an INSERT query,
not an UPDATE query.

On my reading of ODBC SQL minimum grammar, column names
are not escaped and may not contain spaces:

column-identifier ::= user-defined-name
user-defined-name ::= letter[digit | letter | _]
insert-statement ::=
INSERT INTO table-name [( column-identifier [, column-identifier]...)]
VALUES (insert-value[, insert-value]... )

http://msdn.microsoft.com/library/d...c/htm/odbcelements_used_in_sql_statements.asp

However, ODBC allows other ANSI 92 compatible features,
so the fact that the Minimum Grammar does not support
quoted identifiers doesn't mean that they are never legal:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/odbc/htm/odbcsqlgetinfo.asp

(david)

AFAIK, ADO does not generate an SQL statement when you're using a recordset
to update.

However, looking at answers given elsewhere in this thread, I don't
understand what you're trying to do at all. Why are you using something like
this to update data in the database where the code is running? You don't use
DSNs in that situation.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Nope, the code is exactly the same. ADO generates the SQL statement to
perform this operation. And the SQL statement that ADO is generating is not
putting in the field name placeholders hence the error message....

I don't understand. The code you posted didn't use an INSERT INTO statement:
it had a recordset based on a table, and was using .AddNew. Have you changed
your code since your original question?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



I got the same error message:
[Microsoft][ODBC Microsoft Access Driver] Syntax error in INSERT INTO
statement

when I click on debug it highlights the .update statement

Any ideas??
Any way I could see what the actual SQL statement gets generated?

:

See whether

.Fields("Bus PC") = txtPC

works.

On the other hand, if you have the ability to change the field names,
I'd
strongly recommend that. Use of a third part product to ensure you catch
all
references to the old field names would probably be advisable:

Find & Replace: http://www.rickworld.com/products.html
Speed Ferret: http://www.moshannon.com/speedferret.html
Ucora's: http://www3.bc.sympatico.ca/starthere/findandreplace/


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



Using the recordset object, and a connetion object, I can

connect
to a spaces,

I

am
unable to update.
Following is the code I am using in the onclick event
You will notice I have commented out the field names that have spaces
as
that generates a syntax error...
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.
Is there a way I can update those columns that have spaces in the
fieldnames?

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

&

 

[david epsom dot com dot au]

If you can see that the placeholders are not in the generated
SQL, why don't you just try adding them?


..fields("""Tax Status""")

(assuming ANSI 92 quotes used on identifiers)

(david)

Nope, the code is exactly the same. ADO generates the SQL statement to
perform this operation. And the SQL statement that ADO is generating is not
putting in the field name placeholders hence the error message....

I don't understand. The code you posted didn't use an INSERT INTO statement:
it had a recordset based on a table, and was using .AddNew. Have you changed
your code since your original question?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

I got the same error message:
[Microsoft][ODBC Microsoft Access Driver] Syntax error in INSERT INTO
statement

when I click on debug it highlights the .update statement

Any ideas??
Any way I could see what the actual SQL statement gets generated?

:

See whether

.Fields("Bus PC") = txtPC

works.

On the other hand, if you have the ability to change the field

names,
I'd

strongly recommend that. Use of a third part product to ensure you

catch
all

references to the old field names would probably be advisable:

Find & Replace: http://www.rickworld.com/products.html
Speed Ferret: http://www.moshannon.com/speedferret.html
Ucora's: http://www3.bc.sympatico.ca/starthere/findandreplace/


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



Using the recordset object, and a connetion object, I can connect to a
database and add records, howevere where the fieldname has spaces,

I
am

unable to update.
Following is the code I am using in the onclick event
You will notice I have commented out the field names that have

spaces
as

that generates a syntax error...
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.
Is there a way I can update those columns that have spaces in the
fieldnames?

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"

 

[Alex Ivanov]

If it is a local database, you'd better use DAO. Why do you need a middle
layer?
dim rs as dao.recordset
set rs=currentdb.openrecordset(...)
with rs
'the rest is basically the same.

--
Please reply to NG only. This email is not monitored.
Alex.

If you can see that the placeholders are not in the generated
SQL, why don't you just try adding them?


.fields("""Tax Status""")

(assuming ANSI 92 quotes used on identifiers)

(david)

Nope, the code is exactly the same. ADO generates the SQL statement to
perform this operation. And the SQL statement that ADO is generating is not
putting in the field name placeholders hence the error message....

I don't understand. The code you posted didn't use an INSERT INTO statement:
it had a recordset based on a table, and was using .AddNew. Have you changed
your code since your original question?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



I got the same error message:
[Microsoft][ODBC Microsoft Access Driver] Syntax error in INSERT INTO
statement

when I click on debug it highlights the .update statement

Any ideas??
Any way I could see what the actual SQL statement gets generated?

:

See whether

.Fields("Bus PC") = txtPC

works.

On the other hand, if you have the ability to change the field names,
I'd
strongly recommend that. Use of a third part product to ensure you catch
all
references to the old field names would probably be advisable:

Find & Replace: http://www.rickworld.com/products.html
Speed Ferret: http://www.moshannon.com/speedferret.html
Ucora's: http://www3.bc.sympatico.ca/starthere/findandreplace/


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



Using the recordset object, and a connetion object, I can connect to a
database and add records, howevere where the fieldname has
spaces, I
am
unable to update.
Following is the code I am using in the onclick event
You will notice I have commented out the field names that have spaces
as
that generates a syntax error...
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.
Is there a way I can update those columns that have spaces in the
fieldnames?

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"

 

[cityofgp]

Good point. The only reason for that is because then I have to go to each
individual pc and add the reference to the DAO class...

If it is a local database, you'd better use DAO. Why do you need a middle
layer?
dim rs as dao.recordset
set rs=currentdb.openrecordset(...)
with rs
'the rest is basically the same.

--
Please reply to NG only. This email is not monitored.
Alex.

If you can see that the placeholders are not in the generated
SQL, why don't you just try adding them?


.fields("""Tax Status""")

(assuming ANSI 92 quotes used on identifiers)

(david)

Nope, the code is exactly the same. ADO generates the SQL statement to
perform this operation. And the SQL statement that ADO is generating is not
putting in the field name placeholders hence the error message....


:

I don't understand. The code you posted didn't use an INSERT INTO statement:
it had a recordset based on a table, and was using .AddNew. Have you changed
your code since your original question?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



I got the same error message:
[Microsoft][ODBC Microsoft Access Driver] Syntax error in INSERT INTO
statement

when I click on debug it highlights the .update statement

Any ideas??
Any way I could see what the actual SQL statement gets generated?

:

See whether

.Fields("Bus PC") = txtPC

works.

On the other hand, if you have the ability to change the field names,
I'd
strongly recommend that. Use of a third part product to ensure you catch
all
references to the old field names would probably be advisable:

Find & Replace: http://www.rickworld.com/products.html
Speed Ferret: http://www.moshannon.com/speedferret.html
Ucora's: http://www3.bc.sympatico.ca/starthere/findandreplace/


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



Using the recordset object, and a connetion object, I can connect to a
database and add records, howevere where the fieldname has
spaces, I
am
unable to update.
Following is the code I am using in the onclick event
You will notice I have commented out the field names that have spaces
as
that generates a syntax error...
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.
Is there a way I can update those columns that have spaces in the
fieldnames?

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"

 

[Douglas J. Steele]

No, you don't. References are part of the MDB: you add it once, and it
applies to all machines that use that database. (Of course, you need to have
the same version of the referenced file in the same location on each
machine, or you can run into problems)

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Good point. The only reason for that is because then I have to go to each
individual pc and add the reference to the DAO class...

If it is a local database, you'd better use DAO. Why do you need a middle
layer?
dim rs as dao.recordset
set rs=currentdb.openrecordset(...)
with rs
'the rest is basically the same.

--
Please reply to NG only. This email is not monitored.
Alex.

If you can see that the placeholders are not in the generated
SQL, why don't you just try adding them?


.fields("""Tax Status""")

(assuming ANSI 92 quotes used on identifiers)

(david)

Nope, the code is exactly the same. ADO generates the SQL statement to
perform this operation. And the SQL statement that ADO is generating is
not
putting in the field name placeholders hence the error message....


:

I don't understand. The code you posted didn't use an INSERT INTO
statement:
it had a recordset based on a table, and was using .AddNew. Have you
changed
your code since your original question?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



I got the same error message:
[Microsoft][ODBC Microsoft Access Driver] Syntax error in INSERT INTO
statement

when I click on debug it highlights the .update statement

Any ideas??
Any way I could see what the actual SQL statement gets generated?

:

See whether

.Fields("Bus PC") = txtPC

works.

On the other hand, if you have the ability to change the field
names,
I'd
strongly recommend that. Use of a third part product to ensure you
catch
all
references to the old field names would probably be advisable:

Find & Replace: http://www.rickworld.com/products.html
Speed Ferret: http://www.moshannon.com/speedferret.html
Ucora's: http://www3.bc.sympatico.ca/starthere/findandreplace/


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



Using the recordset object, and a connetion object, I can connect
to a
database and add records, howevere where the fieldname has
spaces,
I
am
unable to update.
Following is the code I am using in the onclick event
You will notice I have commented out the field names that have
spaces
as
that generates a syntax error...
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.
Is there a way I can update those columns that have spaces in the
fieldnames?

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"

 

[cityofgp]

Ill try it out.

Thanks.

No, you don't. References are part of the MDB: you add it once, and it
applies to all machines that use that database. (Of course, you need to have
the same version of the referenced file in the same location on each
machine, or you can run into problems)

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Good point. The only reason for that is because then I have to go to each
individual pc and add the reference to the DAO class...

If it is a local database, you'd better use DAO. Why do you need a middle
layer?
dim rs as dao.recordset
set rs=currentdb.openrecordset(...)
with rs
'the rest is basically the same.

--
Please reply to NG only. This email is not monitored.
Alex.


If you can see that the placeholders are not in the generated
SQL, why don't you just try adding them?


.fields("""Tax Status""")

(assuming ANSI 92 quotes used on identifiers)

(david)

Nope, the code is exactly the same. ADO generates the SQL statement to
perform this operation. And the SQL statement that ADO is generating is
not
putting in the field name placeholders hence the error message....


:

I don't understand. The code you posted didn't use an INSERT INTO
statement:
it had a recordset based on a table, and was using .AddNew. Have you
changed
your code since your original question?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



I got the same error message:
[Microsoft][ODBC Microsoft Access Driver] Syntax error in INSERT INTO
statement

when I click on debug it highlights the .update statement

Any ideas??
Any way I could see what the actual SQL statement gets generated?

:

See whether

.Fields("Bus PC") = txtPC

works.

On the other hand, if you have the ability to change the field
names,
I'd
strongly recommend that. Use of a third part product to ensure you
catch
all
references to the old field names would probably be advisable:

Find & Replace: http://www.rickworld.com/products.html
Speed Ferret: http://www.moshannon.com/speedferret.html
Ucora's: http://www3.bc.sympatico.ca/starthere/findandreplace/


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



Using the recordset object, and a connetion object, I can connect
to a
database and add records, howevere where the fieldname has
spaces,
I
am
unable to update.
Following is the code I am using in the onclick event
You will notice I have commented out the field names that have
spaces
as
that generates a syntax error...
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.
Is there a way I can update those columns that have spaces in the
fieldnames?

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"

 

[Alex Ivanov]

The referenced libraries don't need to be in the same location. If they are
registered properly, Windows should be able to find them anywhere, of course
if the user have at least read rights to their location.

--
Please reply to NG only. This email is not monitored.
Alex.

Ill try it out.

Thanks.

No, you don't. References are part of the MDB: you add it once, and it
applies to all machines that use that database. (Of course, you need to
have
the same version of the referenced file in the same location on each
machine, or you can run into problems)

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Good point. The only reason for that is because then I have to go to
each
individual pc and add the reference to the DAO class...


:

If it is a local database, you'd better use DAO. Why do you need a middle
layer?
dim rs as dao.recordset
set rs=currentdb.openrecordset(...)
with rs
'the rest is basically the same.

--
Please reply to NG only. This email is not monitored.
Alex.


message
If you can see that the placeholders are not in the generated
SQL, why don't you just try adding them?


.fields("""Tax Status""")

(assuming ANSI 92 quotes used on identifiers)

(david)

Nope, the code is exactly the same. ADO generates the SQL
statement to
perform this operation. And the SQL statement that ADO is
generating is
not
putting in the field name placeholders hence the error message....


:

I don't understand. The code you posted didn't use an INSERT
INTO
statement:
it had a recordset based on a table, and was using .AddNew. Have you
changed
your code since your original question?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



I got the same error message:
[Microsoft][ODBC Microsoft Access Driver] Syntax error in
INSERT INTO
statement

when I click on debug it highlights the .update statement

Any ideas??
Any way I could see what the actual SQL statement gets
generated?

:

See whether

.Fields("Bus PC") = txtPC

works.

On the other hand, if you have the ability to change the
field
names,
I'd
strongly recommend that. Use of a third part product to
ensure you
catch
all
references to the old field names would probably be
advisable:

Find & Replace: http://www.rickworld.com/products.html
Speed Ferret: http://www.moshannon.com/speedferret.html
Ucora's:
http://www3.bc.sympatico.ca/starthere/findandreplace/


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



Using the recordset object, and a connetion object, I can connect
to a
database and add records, howevere where the fieldname has
spaces,
I
am
unable to update.
Following is the code I am using in the onclick event
You will notice I have commented out the field names that have
spaces
as
that generates a syntax error...
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.
Is there a way I can update those columns that have spaces
in the
fieldnames?

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"

 

[david epsom dot com dot au]

The referenced libraries don't need to be in the same location. If

registered properly, Windows should be able to find them anywhere,

But 'should' isn't 'can'.

DAO is normally installed in
"C:\Program Files\Common Files\Microsoft Shared\dao"

If library files aren't installed in the expected location,
Windows can normally find them, load them, and crash your
Access application in odd and unusual ways.

(david)

The referenced libraries don't need to be in the same location. If they are
registered properly, Windows should be able to find them anywhere, of course
if the user have at least read rights to their location.

--
Please reply to NG only. This email is not monitored.
Alex.

Ill try it out.

Thanks.

No, you don't. References are part of the MDB: you add it once, and it
applies to all machines that use that database. (Of course, you need to
have
the same version of the referenced file in the same location on each
machine, or you can run into problems)

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



Good point. The only reason for that is because then I have to go to
each
individual pc and add the reference to the DAO class...


:

If it is a local database, you'd better use DAO. Why do you need a
middle
layer?
dim rs as dao.recordset
set rs=currentdb.openrecordset(...)
with rs
'the rest is basically the same.

--
Please reply to NG only. This email is not monitored.
Alex.


message
If you can see that the placeholders are not in the generated
SQL, why don't you just try adding them?


.fields("""Tax Status""")

(assuming ANSI 92 quotes used on identifiers)

(david)

Nope, the code is exactly the same. ADO generates the SQL
statement
to
perform this operation. And the SQL statement that ADO is
generating
is
not
putting in the field name placeholders hence the error message....


:

I don't understand. The code you posted didn't use an INSERT
INTO
statement:
it had a recordset based on a table, and was using .AddNew. Have
you
changed
your code since your original question?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



I got the same error message:
[Microsoft][ODBC Microsoft Access Driver] Syntax error in
INSERT
INTO
statement

when I click on debug it highlights the .update statement

Any ideas??
Any way I could see what the actual SQL statement gets
generated?

:

See whether

.Fields("Bus PC") = txtPC

works.

On the other hand, if you have the ability to change the
field
names,
I'd
strongly recommend that. Use of a third part product to
ensure
you
catch
all
references to the old field names would probably be
advisable:

Find & Replace: http://www.rickworld.com/products.html
Speed Ferret: http://www.moshannon.com/speedferret.html
Ucora's:
http://www3.bc.sympatico.ca/starthere/findandreplace/


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



message
Using the recordset object, and a connetion object, I can
connect
to a
database and add records, howevere where the fieldname has
spaces,
I
am
unable to update.
Following is the code I am using in the onclick event
You will notice I have commented out the field names that
have
spaces
as
that generates a syntax error...
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.
Is there a way I can update those columns that have spaces
in
the
fieldnames?

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"

 

[Alex Ivanov]

Sometimes it happens, but usually I trace this kind of problems if some
third-pary installers install different versions of mdac files and/or fail
to register them properly.

--
Please reply to NG only. This email is not monitored.
Alex.

The referenced libraries don't need to be in the same location. If
registered properly, Windows should be able to find them anywhere,

But 'should' isn't 'can'.

DAO is normally installed in
"C:\Program Files\Common Files\Microsoft Shared\dao"

If library files aren't installed in the expected location,
Windows can normally find them, load them, and crash your
Access application in odd and unusual ways.

(david)

The referenced libraries don't need to be in the same location. If they are
registered properly, Windows should be able to find them anywhere, of course
if the user have at least read rights to their location.

--
Please reply to NG only. This email is not monitored.
Alex.

Ill try it out.

Thanks.

:

No, you don't. References are part of the MDB: you add it once, and it
applies to all machines that use that database. (Of course, you need
to
have
the same version of the referenced file in the same location on each
machine, or you can run into problems)

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



Good point. The only reason for that is because then I have to go
to
each
individual pc and add the reference to the DAO class...


:

If it is a local database, you'd better use DAO. Why do you need a
middle
layer?
dim rs as dao.recordset
set rs=currentdb.openrecordset(...)
with rs
'the rest is basically the same.

--
Please reply to NG only. This email is not monitored.
Alex.


message
If you can see that the placeholders are not in the generated
SQL, why don't you just try adding them?


.fields("""Tax Status""")

(assuming ANSI 92 quotes used on identifiers)

(david)

Nope, the code is exactly the same. ADO generates the SQL
statement
to
perform this operation. And the SQL statement that ADO is
generating
is
not
putting in the field name placeholders hence the error message....


:

I don't understand. The code you posted didn't use an INSERT
INTO
statement:
it had a recordset based on a table, and was using .AddNew. Have
you
changed
your code since your original question?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



I got the same error message:
[Microsoft][ODBC Microsoft Access Driver] Syntax error in
INSERT
INTO
statement

when I click on debug it highlights the .update statement

Any ideas??
Any way I could see what the actual SQL statement gets
generated?

:

See whether

.Fields("Bus PC") = txtPC

works.

On the other hand, if you have the ability to change the
field
names,
I'd
strongly recommend that. Use of a third part product to
ensure
you
catch
all
references to the old field names would probably be
advisable:

Find & Replace: http://www.rickworld.com/products.html
Speed Ferret: http://www.moshannon.com/speedferret.html
Ucora's:
http://www3.bc.sympatico.ca/starthere/findandreplace/


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



message
Using the recordset object, and a connetion object, I can
connect
to a
database and add records, howevere where the fieldname has
spaces,
I
am
unable to update.
Following is the code I am using in the onclick event
You will notice I have commented out the field names that
have
spaces
as
that generates a syntax error...
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.
Is there a way I can update those columns that have spaces
in
the
fieldnames?

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"