Member Avatar for Ross Cox

I have the following sql statement in VB6 (latest VB SP, Win XP, SP2) that works fine in ado to sql server 2k5 but does not work in ado to access 2k3. "Select ID, Make, Model, PlsPerBBL, Size, Processor, Location, From MeterData Where MeterNo = 'FQ-805 8" IN'". I know it has a double quote for inches in the MeterNo string, but running the query directly in access works fine. This sql is querying 2 different tables. The one in SQL Server has the ID field as an identity primary key that is seeded with a 1 and incremented by 1 each record, integer data type. The other table is in Access and the ID field is a primary key but is not an auto number data type, it is a long integer. The reason for this is that the Access database contains the same data as the SQL Server database but must be used when the laptop is disconnected from the network where the SQL Server DB is located then added/changed data is uploaded when reconnected to the network. In order to control the SQL Server identity primary key and copy the data to the access database, I could not use an auto number field for the primary key in the Access table.

Help please! rcox@eprod.com

  • 3 Contributors
  • 2 Replies
  • 130 Views
  • 17 Hours Discussion Span
  • Latest Post Latest Post by cometburn
Member Avatar for debasisdas

You need to enclose the quotes by enclosing quotes since it is a string.

Member Avatar for cometburn 
'this is your string
"Select ID, Make, Model, PlsPerBBL, Size, Processor, Location, From MeterData Where MeterNo = 'FQ-805 8" IN'"

Location has a comma plus correct MeterNo value to '"FQ-805 8 IN"'

'correction 
"Select ID, Make, Model, PlsPerBBL, Size, Processor, Location From MeterData Where MeterNo = 'FQ-805 8 IN'"
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.