Connect says 'no records found' even though there are records

[OliverR]

Hi guys,

This is doing my head in. I want to set up a sync profile that syncs from an Access query to a TracerPlus session. The query works fine in Access, and I can also see the data fine in the Data Viewer in Connect Management Studio. However, when I hit the sync button, it says 'no records found'. If I try it with the Event Viewer open, I get the following output:

[10-11-17|10:45:56.650 - 9.2.0.1388 - CriticalError]: **Exception received: ERROR [07002] [Microsoft][ODBC Microsoft Access-stuurprogramma] There are too few parameters. The expected number is: 3.
Stack Trace:
   bij System.Data.Odbc.OdbcConnection.HandleError(OdbcHandle hrHandle, RetCode retcode)
   bij System.Data.Odbc.OdbcCommand.ExecuteReaderObject(CommandBehavior behavior, String method, Boolean needReader, Object[] methodArguments, SQL_API odbcApiMethod)
   bij System.Data.Odbc.OdbcCommand.ExecuteReaderObject(CommandBehavior behavior, String method, Boolean needReader)
   bij System.Data.Odbc.OdbcCommand.ExecuteReader(CommandBehavior behavior)
   bij System.Data.Odbc.OdbcCommand.ExecuteDbDataReader(CommandBehavior behavior)
   bij System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior)
   bij System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)
   bij System.Data.Common.DbDataAdapter.Fill(DataTable[] dataTables, Int32 startRecord, Int32 maxRecords, IDbCommand command, CommandBehavior behavior)
   bij System.Data.Common.DbDataAdapter.Fill(DataTable dataTable)
   bij PTS.Data.PTS_DataSet_ODBC.refreshData(Boolean bOnlyAssignedColumns, Boolean bApplyFilters, Boolean bIsSorted)
Comment: Error during complete ODBC.Fill

The query in Access isn't anything special; it's a view to see open loans in a checkout/checkin system. Basically it retrieves data from 3 tables (Inventory, Users, Checkouts) and has a WHERE clause to only show the checkouts where the ReturnDate field is blank.

Any idea why this isn't working? I've tried going around this by pulling data from a different query, but I need to filter on fields that are empty in the database, and that doesn't seem to want to work, either. Ideally, though, I would just like to retrieve the data from this particular query.

Thoughts or tips?

 

[Joseph Kraebel]

Hi Oliver,

I just tried this in our sample database by creating a 2 field query off of a single table and this worked for me. Based on the error I am wondering if there is an issue with the Query. Maybe someone more familiar with Queries can chime in.

Is this Query coming from tables local in the database as I know we have had issues with running queries off of linked tables.

 

[OliverR]

Hi Joseph, thanks for your reply. Yes, all tables are local in the database, . I've never had issues in the past with syncing from queries or views. I would think if there was a problem with the query, that the data viewer wouldn't show me anything. But I can see the records fine in the Data Viewer, so I would think that means that Connect can "read" the query fine, no?

 

[OliverR]

I've found the problem. I had unchecked a field in the Query Designer window, because I didn't need that to be visible, but I needed to filter on it so that's why I included it. If I check the box to have it visible, syncing works fine.