Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

How do I connect to the default database in DAO?

Status
Not open for further replies.

kkomar

Programmer
May 31, 2007
4
US
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!
 
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
 
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!
 
Hi,

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

Code:
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
 
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 :(
 
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
 
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
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top