Access question: Resolved

I have a VB6 app that calls an access query.

Inside the access query is a function that I have built in the access db.

My problem is when I run the VB app the access VBA code editor opens and I have to step through the function to continue running the app.

Does anybody know how to stop this?

You could make a macro to run the vba function or procedure. Then in vb6 you can call the ...

VB Code:

DoCmd.RunMacro "MacroName", "RepeatCount", "RepeatExpression"

The function creates a value that is a condition in the query. Can a macro accomplish this?

Macros can do allot of things. If you need to pass a value to a query that you are calling from vb then you may want to look into creating a parameter to pass to the query instead?

The query exports to a spreadsheet. The function gets the previous business day and uses that in the query. Nothing is passed from vb.

That doesnt sound like it needs a function call to create a value then? It can all be done it the query except for the exporting. That could be done from a Macro and have the macro call the query first.

I am using DoCmd.TransferSpreadsheet to do the export.

How would the query create the value if not by a function?

You can change the query to a Make-Table query. Then the DoCmd.TransferSpreadsheet would use this new table to transfer.

Tried it and the vba editor still opens.

Can you post your code?

Here is the code:


oAccess.OpenCurrentDatabase "weekly dashboard.mdb"
oAccess.DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "qryBuckets_Crosstab", wbBuckets
oAccess.CloseCurrentDatabase


Here is the query:

SELECT Duration_H_weekend.Segment, Duration_H_weekend.FullName, Sum(Duration_H_weekend.[In Handl]) AS [SumOfIn Handl], Sum(Duration_H_weekend.[Total Duration]) AS [SumOfTotal Duration], Duration_H_weekend.Weekend
FROM Duration_H_weekend INNER JOIN [ISA Table] ON Duration_H_weekend.[ACD EXT] = [ISA Table].[ACD EXT]
GROUP BY Duration_H_weekend.Segment, Duration_H_weekend.FullName, Duration_H_weekend.Weekend
HAVING (((Duration_H_weekend.Segment) Not Like "AYG" And (Duration_H_weekend.Segment) Not Like "TERM") AND ((Sum(Duration_H_weekend.[In Handl]))>0) AND ((Duration_H_weekend.Weekend)=getprevbusinessday(Date())));


Here is the function called in the last line of the query:

Public Function GetPrevBusinessDay(DayDate)
If Format(DayDate, "ddd") = "Sun" Then
GetPrevBusinessDay = DayDate - 2
ElseIf Format(DayDate, "ddd") = "Mon" Then
GetPrevBusinessDay = DayDate - 3
Else
GetPrevBusinessDay = DayDate - 1
End If
End Function



Hope this helps, this is driving me nuts!!

Let me offer up my class module to export queries from external databases, and using SQL instead of defined queries...

I have full instructions on another Forum... but I keep getting wrong for posting links to it in threads.. drop me a PM and I'll send you the link..

To get you started.. and this is only a text based file.. you need to copy all the code into a new class module.. call your module something and then include reference's to both MS Excel and DAO..

Then use code like..

VB Code:

Dim Exp As New clsExport 'or whatever you call it
  With Exp
    .DbName = "weekly dashboard.mdb"
    .FileName = "Spreadsheet you are wanting exported to"
    .SQL = "SELECT Duration_H_weekend.Segment, Duration_H_weekend.FullName, Sum(Duration_H_weekend.[In Handl]) AS [SumOfIn Handl], Sum(Duration_H_weekend.[Total Duration]) AS [SumOfTotal Duration], Duration_H_weekend.Weekend
FROM Duration_H_weekend INNER JOIN [ISA Table] ON Duration_H_weekend.[ACD EXT] = [ISA Table].[ACD EXT]
GROUP BY Duration_H_weekend.Segment, Duration_H_weekend.FullName, Duration_H_weekend.Weekend
HAVING (((Duration_H_weekend.Segment) Not Like 'AYG' And (Duration_H_weekend.Segment) Not Like 'TERM') AND ((Sum(Duration_H_weekend.[In Handl]))>0) AND ((Duration_H_weekend.Weekend)=getprevbusinessday(Date())));"
    .Export
  End With

I'll probably get wrong for suggesting pm'in the link to ya :lol:

That seems like a lot of code to simply export a spreadsheet.

I failed to mention in my post with the code that the query called from the app calls the query with the function in it. I don't know if that makes a difference.

I tried using the macro, with no luck. It still opens the access vba editor.

It's a class module that prompts you for information and then supplies you with the output... Some of the advantages over TransferSpreadsheet are:

using existing spreadsheets and saving them under a different name after export
it will take a SELECT query's SQL rather than having to create the query then do the export...
Browse function on Spreadsheets and Databases
You can Transfer to Excel between databases..

I know the code's a lot, but you don't need to worry about that all you need to do is change a few properties and it does the hard part for you..

This class only allows a select statement.

The query that needs to be exported is a crosstab query. The select query results are called by the crosstab.

It only allows a SELECT query, but it does also take the tablenames as well, in fact you can iterate through the tablename collection in the requested database and export either all of them or just a specific on..

You could make table from the cross tab and then export the resulting table...

That is my problem. When I make the table the code editor opens and I have to step throught the function that is part of the query to make the table.

And what is the actual code you are using to make the table..

It is not code. It is a make table query in access.

Okay so there is a function built into the query.. ahh that'll be this then..

VB Code:

Public Function GetPrevBusinessDay(DayDate)
  If Format(DayDate, "ddd") = "Sun" Then
    GetPrevBusinessDay = DayDate - 2
  ElseIf Format(DayDate, "ddd") = "Mon" Then
    GetPrevBusinessDay = DayDate - 3
  Else
    GetPrevBusinessDay = DayDate - 1
  End If
End Function

There is no return variable on this function and the parameter is not declared.. is this function really needed...

Expr1: IIF(Format(DayDate,"ddd")="Sun",DateAdd("d",DayDate,-2),IIF(Format(DayDate,"ddd")="Mon",DateAdd("d",DayDate,-3),DateAdd("d",DayDate,-1)))

Try that in your query.. change the daydate for the date field..

That worked. Thanks.

Do you know if there is a setting in Access that will cause the code editor to open. There will be a need in the future to use a function and I would like to know if this is possible.

Thanks again.

place the word STOP at the begining of the procedure, when the code hits this line it will go into break mode.