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 Westi on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

ADO for Excel Connection to access

Status
Not open for further replies.

101287

MIS
Apr 8, 2006
189
US
I would like to connect to an MS Access Database 2007 (Office 2007) and open the database and execute a query. I have the following code in Excel but I'm not able to get the database open and/or query to execute. Can someone let me know what I'm doing incorrectly.Get object error in Set MyRecordset.

Thank you for guidance.

Luis
+++++++++++++++++++++++++++++++++++++++++++++++++++
Dim MyDatabase As DAO.Database
Dim MyQueryDef As DAO.QueryDef
Dim MyRecordset As DAO.Recordset
Set MyDatabase = DBEngine.OpenDatabase(DbName)
Set MyQueryDef = MyDatabase.QueryDefs("qryInfRequirementsMetrics")
Set MyRecordset = MyQueryDef.OpenRecordset
+++++++++++++++++++++++++++++++++++++++++++++++++++++
 


hi,

ADO or DAO? You are not consistent!

You ought to use ADO from Excel to access an Access DB.

Exa:
Code:
Function GetHrsOfOper(sResource As String, dDateIn As Date)
    Dim sConn As String, sSQL As String
    Dim rst As ADODB.Recordset, cnn As ADODB.Connection
    Dim sPath As String, sDB As String
    
    sPath = "\\bhdfwfp426.bh.com\M_Ctr$\1_Supply Chain\FP\Procedures\NewAdmin"
    sDB = "APS UNIVERSE"
    
    Set cnn = New ADODB.Connection

    cnn.Open "Provider=Microsoft.ACE.OLEDB.12.0;" & _
        "Data Source=C:\myFolder\myAccess2007file.accdb;" & _
        "Persist Security Info=False;"
    
    Set rst = New ADODB.Recordset
    
    sSQL = "SELECT (1-TypeValue)*24 "

    sSQL = sSQL & "FROM Resource_calendar_data "

    sSQL = sSQL & "WHERE Resource Like '" & sResource & "%'"
    sSQL = sSQL & "  AND #" & Format(dDateIn, "mm/dd/yyyy") & "# >=FromDate"
    sSQL = sSQL & "  AND #" & Format(dDateIn, "mm/dd/yyyy") & "# <=ToDate"
        
    With rst
        .Open sSQL, cnn, adOpenStatic, adLockReadOnly, adCmdText
        On Error Resume Next
        .MoveFirst
        If Err.Number = 0 Then
            GetHrsOfOper = rst(0)
        Else
            GetHrsOfOper = 24
        End If
        
        .Close
    End With
    cnn.Close
    
    Set rst = Nothing
    Set cnn = Nothing
End Function


Skip,

[glasses]Just traded in my old subtlety...
for a NUANCE![tongue]
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top