currentdb = nothing?

[kfguardian]

I am trying to set up a querydef to export a query. The set qdf statement
doesn't execute because mydb=nothing for some reason. I'm not real familiar
with doing the DAO vs ADO stuff so I'm not sure what I am missing. All
examples I have seen have nothing special in them?! This is an adp instead
of an mdb- does that matter??

Dim mydb As DAO.Database
Dim qdf As DAO.QueryDef

Set cnThisConnect = CurrentProject.Connection
strSQL = "SELECT * FROM tblInputData " _
& "WHERE (InputDate >= CONVERT(DATETIME, '" & datQueryStart
& "', 102)) AND" _
& " (InputDate < CONVERT(DATETIME, '" & ShiftEnd &
"', 102))"

rstNewInputData.Open strSQL, cnThisConnect, adOpenKeyset,
adLockOptimistic, adCmdText

Set mydb = CurrentDb()
Set qdf = CurrentDb.CreateQueryDef("ShiftReport", strSQL)

 

[Allen Browne]

You code declares DAO variables, and then proceeds to use ADO methods. You
need to use one or the other.

Given it's an ADP, you probably want ADO. See this example:
http://allenbrowne.com/func-ADO.html#AdoRecordsetExample

 

[kfguardian]

My ADO stuff does work. What I was trying to do was set up a query to export
my recordset to excel. Because my form is not bound to a table, just using
the export and analyze in Excel do not work- only gives data of current
record for each record in the recordset.

So with that said, how do I output my ADO recordset to Excel? Every example
I have seen uses DAO and I even saw one work with an ADO recordset being
created. Do I have to use some cryptic way of looping through each recordset
or something??

I have been trying to get the answer to this for a few days now so hopefully
you can come through for me! Thanks in advance.

 

[Allen Browne]

I don't understand the question.

TransferSpreadsheet should be much more efficient than looping through a
recordset to export each record. Just craft your SQL statement so it exports
the records you want.

 

[kfguardian]

Transferspreadsheet wants a query name though right? How do I define a query
name (ie strSQL in my code)? That is what I was trying to do with the DAO
querydef. How else can I do it or can I get the currentdb part to work from
the original question... even though it is DAO.

 

[Allen Browne]

Assuming you already have a saved query named (say) qry4Export, you can
modify its SQL property with:
strSql = "SELECT ...
CurrentDb.QueryDefs.SQL = strSql

 

[kfguardian]

I do not have a saved query. I build it in the code since it dependent on
other variables.

As I mentioned in my original problem, currentdb is 'empty' when I run the
code. Do you know what could be causing that??

 

[kfguardian]

Sorry it says currentdb=nothing not empty...

Assuming you already have a saved query named (say) qry4Export, you can
modify its SQL property with:
strSql = "SELECT ...
CurrentDb.QueryDefs.SQL = strSql

 

[Stefan Hoffmann]

hi,

examples I have seen have nothing special in them?! This is an adp instead
of an mdb- does that matter??

Yes, take a closer look at the OH:

CurrentDb returns an "instance" to a .mdb, thus it is not defined for an
..adp.


mfG
--> stefan <--

 

[Allen Browne]

Didn't understand that response.

(BTW, Nothing and Empty are not the same in Access.)