How do I connect to the default database in DAO?

[kkomar]

I'm trying to connect to the default DB in DAO so i cna create a temp query and do the following simple action:
-------------
Function SQLToExcel(sqlString As String)
Dim cnn As DAO.Connection
Dim qdf As DAO.QueryDef

Set cnn = CurrentDb

Set qdf = cnn.CreateQueryDef(Null, sqlString)
DoCmd.OutputTo acOutputQuery, qdf.Name, acFormatXLS, , True
Set qdf = Nothing
Set cnn = Nothing

End Function
-----------------
The above code gives error 91 object variable or with block variable not set on the line that starts "Set qdf = cnn.CreateQueryDef...". WHat am I doing wrong? I've tried this so many other ways, I've run out of steam on it.
ANy help is appreciated. Thanks!

 

[PHV]

Function SQLToExcel(sqlString As String)
Dim cnn As DAO.[!]Database[/!]
Dim qdf As DAO.QueryDef
Set cnn = CurrentDb
Set qdf = cnn.CreateQueryDef([!]""[/!], sqlString)
DoCmd.OutputTo acOutputQuery, qdf.Name, acFormatXLS, , True
Set qdf = Nothing
Set cnn = Nothing
End Function

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[kkomar]

I dont know if my references are wrong or what, but I still get the same error, even with the code in the way that you mentioned there. I actually had it like that previously and then changed it.
I have DAO 3.6 up above ado etc etc... but i always get that error...
Is there some lame thing I am missing possibly?
Thanks in advance!

 

[Rofeu]

Hi,

not particularly familiar with DAO, but do you not need to connect first?

Function SQLToExcel(sqlString As String)
Dim cnn As DAO.Database
Dim qdf As DAO.QueryDef
    Set cnn = CurrentDb
    cnn.Connect
    Set qdf = cnn.CreateQueryDef("", sqlString)
    DoCmd.OutputTo acOutputQuery, qdf.Name, acFormatXLS, , True
    Set qdf = Nothing
    Set cnn = Nothing
End Function

cheers,

Roel

 

[kkomar]

No because this code is contained within a database which already has a connection open to a SQL server. CurrentDB is supposed to be a handle to that connection which you can point to.
However, at this point, I'll take any suggestions because nothing seems to be working

 

[PHV]

database which already has a connection open to a SQL server
MDB or ADP ?

Have you tried this ?
Set qdf = Application.CurrentDb.CreateQueryDef("", sqlString)

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[kkomar]

I just tried it and I get the same thing.
Still going at it though, I won't give up! Here's what I have so far...

Public Sub SQLToExcel(sqlString As String)
Dim cnn As DAO.Database
Dim qdf As DAO.QueryDef

Set cnn = Application.CurrentDb
Set qdf = cnn.CreateQueryDef("", sqlString)
DoCmd.OutputTo acOutputQuery, qdf.Name, acFormatXLS, , True
Set qdf = Nothing
Set cnn = Nothing

End Sub