Converting DLookups in Access DB Backended in SQL Server

[RoadrunnerII]

Hi
Just joined this forum looking for some help on a small ( 11Meg) Access database.
I do not have a SQL server background
I work in a larger organization where we have a DBA group but they do not support the migration from Access to SQL server. They support the actual database only No query support on a migration.

I have just reworked an Access database to provide additional functionality. We need to share the data Nationally so we want to backend the database on a SQL server to provide all the benefits of SQL server. I have already run through the simple queries and made them Passthrough queries.
The issue I have is I inherited about 15 queries with the same Dlookup in each of them
The Access forms actually have embedded DLookups as well
Looking for a solution that would allow a more cut and paste fix as the Dlookups are all the same
Here is an example of one of the queries:

SELECT SerializedInventory.InventoryStatus, SerializedInventory.UsageStatus, SerializedInventory.InventoryType, SerializedInventory.Manufacturer, SerializedInventory.ModelName, SerializedInventory.SerialNumber, SerializedInventory.PinNumber, SerializedInventory.MACAddress, SerializedInventory.EndofLife, SerializedInventory.Group, InventoryType.Type
FROM InventoryType INNER JOIN SerializedInventory ON InventoryType.TypeID = SerializedInventory.InventoryType
WHERE (((SerializedInventory.InventoryStatus)="undetermined" Or (SerializedInventory.InventoryStatus)="current inventory") AND ((SerializedInventory.EndofLife)=0) AND ((SerializedInventory.Group)=DLookUp("[defswitchboard]","tblDefaults")));

I have already found the downfall of the DLookup's in that they create a large amount of traffic between the workstation and server
Would really like to get them out of the code as passing the data across the country really slows down the interface response

Thanks
RRII

 

[pbaldy]

Try

...SerializedInventory.Group= (SELECT defswitchboard FROM tblDefaults)

 

[Banana]

I wonder if ElookUp functions with some modification to work with SQL server instead would be worth the investment?

 

[pbaldy]

If I understand the goal here correctly, I don't think so. I believe the goal is to have something that can run on the server (pass through query or stored procedure). Allen's function, while excellent, would not be recognized by SQL Server any more than DLookup is. An alternative would be a UDF equivalent on the server, but I suspect the subquery will be just as fast. You could also write a stored procedure that got the value into a variable and then used it.

 

[Banana]

Thanks- I was kinda of wondering about that one- I know that UDF won't go on servers, but since ELookup used SQL, I thought that would be a possibility.

 

[boblarson]

I know that UDF won't go on servers

Banana:

Can you explain what you mean by this? I'm confused because I use UDF's on a SQL Server all of the time.

 

[pbaldy]

Well, presuming you pass the ELookup the same way the DLookup is being passed, the server won't know what it is (since it's not T-SQL). You're trying to call a function that the server doesn't know how to find, because it lives in the Access side. You could write a SQL Server UDF that did the same thing, but I'm not sure you gain anything in this case.

 

[pbaldy]

I think he meant a VBA UDF Bob, like ELookup.

 

[Banana]

Bob- What pbaldy said, I wasn't being precise enough. My apologies.

Pbaldy, that makes sense- even if the function is just vanilla SQL, it doesn't really matter since the key here is the location of function and with Elookup, it's on the wrong side of track, so to speak.

 

[RoadrunnerII]

One more time

Just to clarify Yes I am doing Passthrough queries to get the workload on the SQL server instead of the Workstation Saves the network traffic back and forth

Well the SubSelect statement works for some of the queries
Thanks PBaldy

The one below is making me scratch my head
It's a bit deep for my knowledge level
Any help much appreciated!
RRII


SELECT SerializedInventoryTransactions.Notes, qrySerializedInventoryByGroup.InventoryStatus, qrySerializedInventoryByGroup.UsageStatus, qrySerializedInventoryByGroup.ModelName, SerializedInventoryTransactions.DateAllocated, SerializedInventoryTransactions.RecipientName, SerializedInventoryTransactions.AllocateReference, SerializedInventoryTransactions.SerialNumber, qrySerializedInventoryByGroup.WorkstationNumber, qrySerializedInventoryByGroup.Group, qrySerializedInventoryByGroup.EndofLife
FROM qrySerializedInventoryByGroup LEFT JOIN SerializedInventoryTransactions ON qrySerializedInventoryByGroup.SerialNumber = SerializedInventoryTransactions.SerialNumber
WHERE (((SerializedInventoryTransactions.Notes)="Current User") AND ((qrySerializedInventoryByGroup.InventoryStatus)="Allocated to Client") AND ((qrySerializedInventoryByGroup.ModelName)="EVO NC6220") AND ((SerializedInventoryTransactions.DateAllocated)>#1/1/2008#) AND ((qrySerializedInventoryByGroup.Group)=DLookUp("[defswitchboard]","tblDefaults")) AND ((qrySerializedInventoryByGroup.EndofLife)=0))
ORDER BY qrySerializedInventoryByGroup.ModelName, SerializedInventoryTransactions.SerialNumber;

 

[pbaldy]

I'm not sure I see what the problem is other than the DLookup. Or is it because you're joining to a query that wouldn't exist in SQL Server? If so, what does that query do?

 

[RoadrunnerII]

Head scratcher

So here is the query with the Dlookup replaced and I have switched it to a passthrough query
SELECT [SerializedInventoryTransactions].[Notes], [qrySerializedInventoryByGroup].[InventoryStatus], [qrySerializedInventoryByGroup].[UsageStatus], [qrySerializedInventoryByGroup].[ModelName], [SerializedInventoryTransactions].[DateAllocated], [SerializedInventoryTransactions].[RecipientName], [SerializedInventoryTransactions].[AllocateReference], [SerializedInventoryTransactions].[SerialNumber], [qrySerializedInventoryByGroup].[WorkstationNumber], [qrySerializedInventoryByGroup].[Group], [qrySerializedInventoryByGroup].[EndofLife]
FROM [qrySerializedInventoryByGroup] LEFT JOIN [SerializedInventoryTransactions] ON [qrySerializedInventoryByGroup].[SerialNumber] = [SerializedInventoryTransactions].[SerialNumber]
WHERE ((([SerializedInventoryTransactions].[Notes])='Current User') AND ([qrySerializedInventoryByGroup].[InventoryStatus])='Allocated to Client') AND (([qrySerializedInventoryByGroup].[ModelName])='EVO NC6220') AND (([SerializedInventoryTransactions].[DateAllocated])>#1/1/2008#)
AND ([qrySerializedInventoryByGroup].[Group])=(SELECT defswitchboard FROM tblDefaults) AND [qrySerializedInventoryByGroup].[EndofLife]=0
ORDER BY [qrySerializedInventoryByGroup].[ModelName], [SerializedInventoryTransactions].[SerialNumber];

Here is the error I am getting
ODBC Call Failed........
Incorrect Syntax near '#'. (#102) Microsoft SQL Server Driver Sql Server Incorrect snytax near the keyword 'AND' (#156)
Now if I just remove the Dlookup and put the SubSelect in the query runs fine
Hmmmm
RRII

 

[pbaldy]

SQL Server will want single quotes around the date rather than #:

[DateAllocated])>'1/1/2008'

 

[RoadrunnerII]

Getting there slowly

Got it
Thanks again PBaldy
So I have managed to get through over a dozen of the queries now and transform them to Passthrough's which is great

Now moving onto my next challenges
Have a couple of queries with nz's in them
I know you can convert to ISNULL or COALESCE
I am just not getting the right syntax

Here is an example query

SELECT [NonSerializedInventoryTransactions].[TransactionType], [NonSerializedInventoryTransactions].[TransactionDate], [NonSerializedInventoryTransactions].[ReceiptReference], [NonSerializedInventoryTransactions].[AllocateReference], [NonSerializedInventoryTransactions].[RecipientName], [NonSerializedInventoryTransactions].[UnitsReceived], [NonSerializedInventoryTransactions].[UnitsAllocated], [NonSerializedInventoryTransactions].[UnitsShrinkage], [NonSerializedInventoryTransactions].[Notes], [NonSerializedInventoryTransactions].[ProductNumber], ((nz([UnitsReceived])-nz([UnitsAllocated])-nz([UnitsShrinkage]))) AS CalcUnits0nHand, [NonSerializedInventoryTransactions].[NonSerializedId]
FROM NonSerializedInventoryTransactions
ORDER BY [NonSerializedInventoryTransactions].[TransactionDate];


Second question is with an embedded Form! in the query like this:

SELECT [SerializedInventory].*
FROM SerializedInventory
WHERE ((([SerializedInventory].[SerialNumber])=Forms!frm_SerializedInventory!SerialNumber));


Third question is when referencing another query like this:

SELECT [NonSerializedInventory].[ProductNumberID], Sum(qrySumUnitsOnHandTotal.[SumOfCalcUnits0nHand]) AS SumOfSumOfCalcUnits0nHand
FROM NonSerializedInventory INNER JOIN qrySumUnitsOnHandTotal ON [NonSerializedInventory].[NonSerializedID] = qrySumUnitsOnHandTotal.[NonSerializedId]
GROUP BY [NonSerializedInventory].[ProductNumberID]
ORDER BY [NonSerializedInventory].[ProductNumberID];


How do I get the syntax right?
If I can get an example I can do the coding but my searches are not giving me the answers i need
Sorry for being a problem child


RRII

 

[pbaldy]

The T-SQL IsNull function is a direct replacement for Nz, so you should just be able to switch them out (but I'm not sure you can leave off the second argument, so make sure you specify it).

Frankly, simple queries that get a criteria from a form I would probably leave as Access queries. Jet is pretty good about passing the criteria to SQL Server, so you'll only get the matching records coming back over the wire anyway. Your options otherwise both involve interpreting the value from the form control and adjusting the SQL accordingly, using a DAO QueryDef.

Is qrySumUnitsOnHandTotal a pass through query, and is the posted SQL a pass through? You can use a pass through query in an Access query like that, but not in another pass through, since SQL Server knows nothing about it.

I already have 2 problem children, and they're a lot more expensive than you are.

 

[RoadrunnerII]

Thanks for great feedback
qrySumUnitsOnHandTotal is not a passthrough Query as it gives me grief if I convert it because it is referencing qryUnitsonHandTotal

Here is the syntax for qrySumUnitsOnHandTotal

SELECT qryUnitsOnHandTOTAL.ProductNumberID, Sum(qryUnitsOnHandTOTAL.CalcUnits0nHand) AS SumOfCalcUnits0nHand, qryUnitsOnHandTOTAL.NonSerializedId
FROM qryUnitsOnHandTOTAL
GROUP BY qryUnitsOnHandTOTAL.ProductNumberID, qryUnitsOnHandTOTAL.NonSerializedId;

Here is the syntax of qryUnitsOnHandTotal query it is referencing:

SELECT Sum(((nz([UnitsReceived])-nz([UnitsAllocated])-nz([UnitsShrinkage])))) AS CalcUnits0nHand, NonSerializedInventoryTransactions.UnitsReceived, NonSerializedInventoryTransactions.UnitsAllocated, NonSerializedInventoryTransactions.UnitsShrinkage, NonSerializedInventoryTransactions.NonSerializedId, NonSerializedInventory.ProductNumberID, NonSerializedInventoryTransactions.TransactionID
FROM NonSerializedInventory INNER JOIN NonSerializedInventoryTransactions ON NonSerializedInventory.NonSerializedID=NonSerializedInventoryTransactions.NonSerializedId
GROUP BY NonSerializedInventoryTransactions.UnitsReceived, NonSerializedInventoryTransactions.UnitsAllocated, NonSerializedInventoryTransactions.UnitsShrinkage, NonSerializedInventoryTransactions.NonSerializedId, NonSerializedInventory.ProductNumberID, NonSerializedInventoryTransactions.TransactionID;

I believe I can get the forms to load faster once I change the qryUnitsOnHandTOTAL to a Passthrough
As the other two rely on the output from this query
Thanks
RRII

 

[pbaldy]

Have you considered making that a View on SQL Server? That might be the most efficient, since it doesn't have parameters. Then you could join to it from pass through queries or link to it and use it directly in the front end.

 

[RoadrunnerII]

The Idea of a view in SQL is great but I wanted to tweak the queries as much as possible first if I can. I don't have the SQL experience to create the views myself
I do have SQL Server Management Studio loaded on my laptop so I can do a bit of manipulation. I also loaded the MSSMA and have messed with it a bit but since I had so many Dlookups figured I better get them out of the way first.
Now working on an issue that is stopping input of new records in the NonSerialized form. Need to address it before I tweak any more. It works fine on the Access backended Tables but when we moved to Linked Tables the Add Record is not working
RRII

 

[RoadrunnerII]

Another DLookup question

Got another stumper for me with Dlookups

Have a form with 4 of the following Control Source similar syntax:
=DLookUp("[Manufacturer]","nonserializedproducts","[ProductNumber] = '" & [Forms]![frm_NonSerializedInventory]![ProductNumberID] & "'")
When the form loads it seems to load these fields the longest
What is the best way to change this syntax to get rid of the Dlookup's?

I tried this but still have the wrong syntax
=(Select ProductNumber FROM nonserializedproducts) = '" & [Forms]![frm_NonSerializedInventory]![ProductNumberID] & "'"

RRII

 

[pbaldy]

Unfortunately you can't have SQL as the control source of a textbox. Are they different fields from the same record?