Query incorrectly returning no records in ADO Julia B 12/2/2008 11:18:04 AM All I've got a piece of code to loop through a recordset on a split database. I'm opening a query, but the code always fails because the recordset recordcount is always -1. However when I run the query directly in Access it returns 26 records. The only thing I can think is that the sql syntax for the query is not good in ADO?? Here's the code: Dim rs As New ADODB.Recordset Dim db As ADODB.Connection Set db = CurrentProject.Connection rs.Open "_TempPNSFormattedIncorrectly", db, adOpenKeyset, adLockOptimistic, adCmdTableDirect 'fails here rs.MoveFirst The sql syntax for query _TempPNSFormattedIncorrectly is: SELECT PartNumbers.PNStored FROM PartNumbers WHERE (((PartNumbers.PNStored) Like "**/**/****")); Any help greatly appreciated. Thanks Julia

RE: Query incorrectly returning no records in ADO Julia B 12/2/2008 11:25:01 AM All Just worked it out. I need to change my query syntax to replace the * with %. So even though, when I run the query directly from access to test it, it shows no records, it does work if run via ADO. Julia "Julia B" wrote: [Quoted Text] > All > > I've got a piece of code to loop through a recordset on a split database. > I'm opening a query, but the code always fails because the recordset > recordcount is always -1. However when I run the query directly in Access it > returns 26 records. The only thing I can think is that the sql syntax for the > query is not good in ADO?? > > Here's the code: > > Dim rs As New ADODB.Recordset > Dim db As ADODB.Connection > Set db = CurrentProject.Connection > rs.Open "_TempPNSFormattedIncorrectly", db, adOpenKeyset, > adLockOptimistic, adCmdTableDirect > 'fails here > rs.MoveFirst > > The sql syntax for query _TempPNSFormattedIncorrectly is: > > SELECT PartNumbers.PNStored > FROM PartNumbers > WHERE (((PartNumbers.PNStored) Like "**/**/****")); > > Any help greatly appreciated. > > Thanks > > Julia

Re: Query incorrectly returning no records in ADO "david" <david[ at ]nospam.au> 12/17/2008 6:02:51 AM You can change the mode of Access so that it expects all queries to use % instead of *. Then this query would also work in Access, but none of your * queries would. (david) "Julia B" <JuliaB[ at ]discussions.microsoft.com> wrote in message news:045DB2A1-B12E-481C-971D-4D2ED36CC46F[ at ]microsoft.com... [Quoted Text] > All > > Just worked it out. I need to change my query syntax to replace the * with > %. So even though, when I run the query directly from access to test it, > it > shows no records, it does work if run via ADO. > > Julia > > "Julia B" wrote: > >> All >> >> I've got a piece of code to loop through a recordset on a split database. >> I'm opening a query, but the code always fails because the recordset >> recordcount is always -1. However when I run the query directly in Access >> it >> returns 26 records. The only thing I can think is that the sql syntax for >> the >> query is not good in ADO?? >> >> Here's the code: >> >> Dim rs As New ADODB.Recordset >> Dim db As ADODB.Connection >> Set db = CurrentProject.Connection >> rs.Open "_TempPNSFormattedIncorrectly", db, adOpenKeyset, >> adLockOptimistic, adCmdTableDirect >> 'fails here >> rs.MoveFirst >> >> The sql syntax for query _TempPNSFormattedIncorrectly is: >> >> SELECT PartNumbers.PNStored >> FROM PartNumbers >> WHERE (((PartNumbers.PNStored) Like "**/**/****")); >> >> Any help greatly appreciated. >> >> Thanks >> >> Julia