Dlookup issues with Query converted to Passthrough Query

[RoadrunnerII]

Hi All
I am new to this forum and still learning MS Access. Hoping this is the right place to ask If not please let me know!
Looking for some help with the SQL statements in Access 2003 with the tables backended into MS SQL Server 2005
The queries work fine but cause the forms to be quite slow if I leave them as is.
If I convert the Queries into Passthrough queries as suggested by a DBA I cannot get the Dlookup below to work
The error I get is ODBC Call Failed ... 'DLookUp' is not a recognized built in function name (#195)
Is there another way to do this simply?
If I plug in one of the values available to the dlookup intop the query after the "=" it works fine and the form refreshes quickly

Thanks
RRII


SELECT [Serialized Inventory].[Inventory Status], [Serialized Inventory].[Usage Status], [Serialized Inventory].[Inventory Type], [Serialized Inventory].Manufacturer, [Serialized Inventory].[Model Name], Count([Serialized Inventory].[Model Name]) AS [CountOfModel Name], [Serialized Inventory].[End of Life], [Serialized Inventory].[Group]
FROM [Serialized Inventory]
GROUP BY [Serialized Inventory].[Inventory Status], [Serialized Inventory].[Usage Status], [Serialized Inventory].[Inventory Type], [Serialized Inventory].[Manufacturer], [Serialized Inventory].[Model Name], [Serialized Inventory].[End of Life], [Serialized Inventory].[Group]
HAVING ((([Serialized Inventory].[Inventory Status])='undetermined ' Or ([Serialized Inventory].[Inventory Status])='current inventory' Or ([Serialized Inventory].[Inventory Status])='In-Storage Inventory') AND (([Serialized Inventory].[End of Life])='0') AND (([Serialized Inventory].[Group])=DLookUp('[defswitchboard]','tblDefaults' )));

[Jim Doherty]

Hi All
I am new to this forum and still learning MS Access. Hoping this is the right place to ask If not please let me know!
Looking for some help with the SQL statements in Access 2003 with the tables backended into MS SQL Server 2005
The queries work fine but cause the forms to be quite slow if I leave them as is.
If I convert the Queries into Passthrough queries as suggested by a DBA I cannot get the Dlookup below to work
The error I get is ODBC Call Failed ... 'DLookUp' is not a recognized built in function name (#195)
Is there another way to do this simply?
If I plug in one of the values available to the dlookup intop the query after the "=" it works fine and the form refreshes quickly

Thanks
RRII


SELECT [Serialized Inventory].[Inventory Status], [Serialized Inventory].[Usage Status], [Serialized Inventory].[Inventory Type], [Serialized Inventory].Manufacturer, [Serialized Inventory].[Model Name], Count([Serialized Inventory].[Model Name]) AS [CountOfModel Name], [Serialized Inventory].[End of Life], [Serialized Inventory].[Group]
FROM [Serialized Inventory]
GROUP BY [Serialized Inventory].[Inventory Status], [Serialized Inventory].[Usage Status], [Serialized Inventory].[Inventory Type], [Serialized Inventory].[Manufacturer], [Serialized Inventory].[Model Name], [Serialized Inventory].[End of Life], [Serialized Inventory].[Group]
HAVING ((([Serialized Inventory].[Inventory Status])='undetermined ' Or ([Serialized Inventory].[Inventory Status])='current inventory' Or ([Serialized Inventory].[Inventory Status])='In-Storage Inventory') AND (([Serialized Inventory].[End of Life])='0') AND (([Serialized Inventory].[Group])=DLookUp('[defswitchboard]','tblDefaults' )));

Taking up on the fact that you are learning Access and thus new my reply might be enough perhaps to send one to sleep but bear with me LOL

'Dlookup' is indeed not a function in SQL Server. I am afraid. You are working on the server side here so SQL servers transact sql language is being used.

In answer to your question I see your dlookup function in access is evidently only seeking it seems to return a single value against 'SerialisedInve ntory.Group' so using a

"SELECT defswitchboard FROM dbo.tbldefaults " in your criteria clause for that 'specific' column would suffice once your syntax is converted.

You will have to rethink not only the syntax but also your SQL syntax generally. If as you say you are learning might I give a few pointers? Can I suggest you change your field and table naming convention? Drop the spaces in field names and table names!. This makes life easier because you will not have to wrap them in square brackets and thus you will have more chars available to you to use in any subsequent SQL on the server side.

Google a search on lechinsky/Roddick naming convention for object naming convention a good standard for Access and not bad taboot for SQL server either

In addition given you are learning and in the process of converting take time to have a look at placing your converted syntax into 'stored procedures' and pass any criteria clauses you have as parameters to the procedure. You can call stored procedure in Access using pass through queries with less syntax and the return will be faster as stored procdures are pre-compiled.

You can use pass dynamic SQL of course but stored procedures should be your ultimate goal.

Converting an Access database generally to satisfy SQL server is not without pain I'm afraid and in so converting you might want to consider some options regarding the interface on the Access side once your table data and SQL is converted.

What are you using mdb files or the ADP format? If you are not aware of the ADP format then have a look at it, because it exposes the 'Views' and stored procedures held on the server 'but' in the Access interface itself and is a good format for interacting with SQL server data.

ADP forms have inbuilt properties to accept input parameters pass them to the server and return data to the 'Access form' that standard mdb files do not have amongst other properties like 'Resynch' and Serverfilter and other properties designed specifically for SQL server.

The ADP format was built by MS specifically to interact with SQL server using universal datalink as opposed to ODBC. You may or may not find them of use to you depends on the complexities and nature of your conversion process. If local tables are required on the Access side then ADP files are not for you.

The preference on MDB or ADP is a personal one with office 2007 seemingly now preferring mdb format

Hope this whilst a little verbose gives you an insight

Regards

Jim :)

[RoadrunnerII]

Thanks for quick reply Jim
I will investigate the suggestions and see what will work