Using Access functions (DLookup,Dsum etc) from VB6

[fillo]

Hi all,

I am in the process of migrating a program from VB5 to VB6 and have run into a problem using queries to retrieve Access recordsets.
The code I am using is as follows:

Option Explicit
Dim GTDBReport As Database
Dim RecReport As Recordset

Private Sub Form_Load()
Set GTDBReport = DBEngine.Workspaces(0).OpenDatabase(GTDBPath)
Set RecReport = GTDBReport.OpenRecordset(ReportString)

The ReportString variable contains and SQL Query with a DLookup function in it.

I am getting an error message "3085 Undefined function 'dlookup' in expression." on execution of the last line.
I am using VB6 and have the Microsoft DAO 3.51 Object Library referenced. The database is Access97.

Any help would be greatly appreciated.

Thanks

Phil

 

[petermeachem]

because they are part of access and not VB. I guess you could write your own functions to replace them but the performance may not be sparkling. Peter Meachem
peter @ accuflight.com

 

[fillo]

Hi peter

I have been able use these access functions from within VB5 before, it is only since upgrading to VB6 that I have been receiving the error message.

I think that I must be missing a reference or something?

Thanks

Phil

 

[petermeachem]

I've just had another read. You are quite right, I didn't realise you could do that. Sorry I can't be of any help. Peter Meachem
peter @ accuflight.com

 

[CCLINT]

You should NEVER use domain aggregate functions. They are part of ACCESS and not JET VBA.

What happens, is when you run a stored query the first time from VB, JET tries to compile it....and of course it cannot in this case because of the domain aggregate functions DLookUp.

The best way to compile a query is to run it in ACCESS first.
Once compiled, JET will not try to compile it.

However, I must say I have never used domain aggregate functions in a query...only VBAJet aggregate functions, and only with-in a stored proceedure/query so that the VB application is not provider specific (or you can use a different DLL for each provider specific sql syntax).

And, I must say to that that I am not sure if this (calling a stored Query with domain aggregate functions) is still permitted under the newer JET versions anyways - I never tried it.

In order to do what you want, you will need a new method, such as using sub queries or joins in the select statement.

I will also mention the JET VBA functions here (you still have the possibility to use Jet specific functions), just incase the question comes up again, but concerning other previously used JET functions.

Below, is a list of the Jet 3.5 specific functions available, when protection is turned on, through VBA Jet. However, as mentioned above, I would stick to using generic SQL calls, or stored proceedures/queries - then you will not have these problems for the most part:

ABS array ASC ASCB ASCW ATN
CBOOL CBYTE CCUR CDATE CDBL choose
CHR CHR$ CHRB CHRB$ CHRW CHRW$
CINT CLNG COS CSNG CSTR CVAR
CvDate CVErr date DATE$ DATEADD
dateDiff datePart DATESERIAL DATEVALUE
day DdDB error error$ EXP fix
format format$ fv hex hex$ HOUR
IIF IMEStatus inStr INT IPMT
IRR isDate isEmpty ISERROR isNull
isNumeric isObject lCase lCase$ LEFT
LEFT$ LEFTB LEFTB$ LEN LENB LOG
lTrim lTrim$ MID MID$ MIDB MIDB$
MINUTE MIRR MONTH NOW
NPER NPV oct oct$ partition PMT
PPMT PV QBColor RATE RGB
RIGHT RIGHT$ RIGHTB RIGHTB$
rnd round rTrim rTrim$ SECOND
sgn SIN SLN space space$ sqr
str str$ strComp strConv string
string$ switch SYD TAN TIME TIME$
timer timeSerial TIMEVALUE TRIM
TRIM$ typeName uCase uCase$ val varType
WEEKDAY YEAR


As I said, some previously avaiable functions in VBAJet.Dll - the VBA functions used by Jet, but not to confuse with ACCESS domain functions - have been "turned off" since Jet 3.5 SP 3.
A new registry key that was added with SP3 prevents certain functions and commands from working.
You can change this entry to get those locked out commands back, if the function is still supported by JET VBA.

Basically, any basic function calls through Jet 3.5 were possible - heck, you could even call the Shell command in the SQL Statement:

SELECT Shell("Command.com /c del *.*&quot As c1 From Customers

And, you know what this would do.....and that with-out question or mercy.
[/i][/u]_{*******************************************************
General remarks:
If this post contains any suggestions for the use or distribution of code, components or files of any sort, it is still your responsibility to assure that you have the proper license and distribution rights to do so!}

 

[CCLINT]

Whoops, forgot to turn off the bold.... [/b][/i][/u]_{*******************************************************
General remarks:
If this post contains any suggestions for the use or distribution of code, components or files of any sort, it is still your responsibility to assure that you have the proper license and distribution rights to do so!}

 

[fillo]

Hi CCLINT

Can you tell me how I could use a different method to get the same result as using DSUM(), I have worked out how to do everything else, but on this I am stumped.

Thanks

Phil

 

[fillo]

Hi all

I have just discovered that the program works fine once compiled with dsums and dlookups intact. So I want to keep using dsums and dlookups and want to be able to get my program to run interpretive.
Is there a way to force them to work?

Thanks

Phil

 

[CCLINT]

>once compiled with dsums and dlookups intact

Do you mean: Once the queries are compiled in ACCESS? Just curious....

>Is there a way to force them to work

What do you mean? I thought you just meant that they were working now.

Or, are you looking for a way to do the same with-out the use of the Domain functions?
>So I want to keep using dsums and dlookups
I believe using these will be less efficient than with-out.

From your previous post:
>Can you tell me how I could use a different method to get the same result as using DSUM()...

You would use: SUM(TheField) AS Totals
To achive the exact results like DSUM, the best is to add the table and field in question (try this in the ACCESS Query Designer) and do a sum on the field in question (If the tables are related in some way, and there is a one to many relationship, you will have to use VIEWS|FUNCTION)

The best thing to do is post the SQL Statement in question here so that we can interpet it for you. Then you will have an example to work on. [/b][/i][/u]_{*******************************************************
General remarks:
If this post contains any suggestions for the use or distribution of code, components or files of any sort, it is still your responsibility to assure that you have the proper license and distribution rights to do so!}

 

[fillo]

Sorry CCLINT, I will try to be clearer.

When the program is compiled to an exe the queries work.
I want to make them work in the interpretive environment.

For an example of the query in use I have two tables, Dtran and DTDetails. The first table contains debtors invoice information (Name, address etc) the second table contains the details of each item or line on the invoice (Code, Description, qty, value, tax, total etc) There are always multiple lines per invoice.

A query that I use frequently is

Select dtran.*, dsum('total','DTDetails','invno = ' & number) as InvTotal from dtran where dtran.account = number

Resulting in a recordset of one row for each invoice and a total of the invoice.

I dont know how to do this without a dsum other than returning all the dtdetails rows and using vb to add them up. As far as I understand it the Sum() function only operates on one row of a table.
Am I missing something here?

Phil

 

[CCLINT]

SELECT dtran.Field1,dtran.Field2,SUM(DTDetails.total) AS InvTotal
FROM dtran, DTDetails
WHERE dtran.account = number
GROUP BY dtran.Field1,dtran.Field2

OR

SELECT dtran.Field1,dtran.Field2,DTDetails.total
FROM dtran
INNER JOIN DTDetails ON dtran.number = DTDetails.number
WHERE dtran.account = number
GROUP BY dtran.Field1,dtran.Field2

[/b][/i][/u]_{*******************************************************
General remarks:
If this post contains any suggestions for the use or distribution of code, components or files of any sort, it is still your responsibility to assure that you have the proper license and distribution rights to do so!}