none
OLEDB connection in Win7 32bit with either Jet nor ACE (Office 2010) RRS feed

  • Question

  • Hello guys,

    I have an add-in that connects to a Microsoft Access Database. Recently, I have installed Win 7, 32 bit and Office 2010(32bit), but while my add-in loads in Excel, I can't get it to connect to the Database. I have tried both Jet and ACE providers with no luck; all these after I have installed the 2010 (and 2007) Access Database Engine from Microsoft. Below is the code, let me know what I'm doing wrong here.

    Best wishes,

    A

    Here's the function:

    Public Function Request(query As String) As Recordset
    ' GenericRequest to the database

    If dbsConn Is Nothing Then
        Set dbsConn = New ADODB.Connection
    End If

    Dim folder As String, fs, f
    Set fs = CreateObject("Scripting.FileSystemObject")

    Dim CompName As String


    'Tes the name of PC to see if connection to G drive or C drive

    If fold = "" Then
    CompName = ReturnComputerName()
    If InStr(CompName, "BONGOS") <> 0 Then
        fold = "C:"
        Else
        fold = "G:"
    End If
    End If

    With dbsConn
    If fs.folderexists(fold & "\Tools\") = True Then
        folder = fold & "\Tools\"
        If InStr(.ConnectionString, fold & "\") = 0 Then
            If .State = 1 Then
                .Close
            End If

            '.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & folder & "database.mdb; Persist Security Info=false"
            .Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & folder & "database.mdb; Persist Security Info=false"
        End If
        ElseIf fs.folderexists("C:\Tools\") = False Then
        folder = "C:\Tools\"
        If InStr(.ConnectionString, "C:\") = 0 Then
            If .State = 1 Then
                .Close
            End If
            '.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & folder & "database.mdb; Persist Security Info=false"
            .Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & folder & "database.mdb; Persist Security Info=false"
        End If
    End If
    End With

    'If dbsConn.State = 0 Then
    '    dbsConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & folder & "database.mdb; Persist Security Info=false"
    'End If


    If cmd Is Nothing Then
        Set cmd = New ADODB.Command
    End If

    cmd.CommandText = query

    Dim rst As ADODB.Recordset
    Set rst = New ADODB.Recordset

    rst.Open cmd.CommandText, dbsConn, adOpenKeyset, adLockOptimistic
       
    Set Request = rst

    End Function

    • Moved by Guang-Ming Bian - MSFT Thursday, August 19, 2010 10:42 AM Redirect to appropriate forum for better support. (From:Visual Basic General)
    Wednesday, August 11, 2010 6:01 PM

Answers