QueryDef

[test1000]

Hello,



I want to do an export to .csv file form a query that ask two question ([ ])
But I've to create a 'config' file otherwise my .csv has not the correct lay-out.
You do that by rightclick on the query - export - Advanced button - set the variable like " and field seperator ;
But when you do a rightclick on a query that ask for two question like [date] and [company] then it's not possible to do.
So I'm looking around for a workaround.

This is the workaround
Dim tmpSql As String
Dim qd As DAO.QueryDef
Dim tmpOutputFile As String
tmpSql = "SELECT * FROM q_lijst" (q_lijst is the query with the 2 questions)
tmpOutputFile = "c:\temp\export20170629.CSV"
Set qd = db.CreateQueryDef("tmpExport", tmpSql)
DoCmd.TransferText acExportDelim, "exportcsv", "tmpExport", tmpOutputFile
db.QueryDefs.Delete "tmpExport"

This code is behind a button. When I push the buttion I get this message.
Dim qd As DAO.QueryDef : Compileerfout : Compilation Error: A user-defined data type is not defined.

What's going wrong ?
What's the problem ?

Thanks and regards

[JoeM]

I am not sure you need to use "DAO".
Try changing:

Dim qd As DAO.QueryDef

to:

Dim qd As QueryDef

and see if that works.

Otherwise, if you want to use DAO, you need to make sure the the Microsoft DAO Object Library is selected in your list of References in VBA.

[Micron]

Best practice IMHO is to always type (as in define their type) your variables, especially when they are objects such as in this case. Otherwise you risk raising run time errors that won't be caught when compiling code. Only when needing to use late binding would I not type a variable. Because you have not included the DAO library in your project references, this error A user-defined data type is not defined. is raised because Access thinks you have declared a custom object without providing any properties for it. IIRC, not specifying the type also robs you of Intellisense, which is what helps you to complete a statement as it provides choices for parameters and properties.

Removing the DAO type casting is the easiest fix, but not the best one.
EDIT: rather than creating/deleting/recreating/... the query def, which can cause bloat and lead to corruption, I would simply make it a temporary query def. You do this by providing "" as its name. Alternatively, you can keep it in the navigation pane, but change its sql property each time.