Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 4 of 4

Thread: Open a database instead of a workbook

  1. #1
    New Member
    Join Date
    Mar 2002
    Posts
    38
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hopefully this will be the last bit of help I'll need for this form. I know how to open a workbook, but how do you open an Access database? What do I use instead of Workbooks.open?

    Thanks in advance for your help.

  2. #2
    New Member
    Join Date
    Apr 2002
    Location
    Kansas City, MO
    Posts
    14
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    This retrieves data from an access database budget.mdb, this DB has one table: "budget" and seven fields. This code retrieves data in the Item field containing the text "lease" and the division field which contains the text " N. America". The qualifying data is stored in a Recordset object, the data is then transferred to the worksheet.

    Sub ADO_Demo()
    ' This demo requires a reference to
    ' the Microsoft ActiveX Data Objects 2.x Library

    Dim DBFullName As String
    Dim Cnct As String, Src As String
    Dim Connection As ADODB.Connection
    Dim Recordset As ADODB.Recordset
    Dim Col As Integer

    Cells.Clear
    MsgBox "This demo retrieves the data for the records in which ITEM = LEASE and DIVISION = N. AMERICA."

    ' Database information
    DBFullName = ThisWorkbook.Path & "budget.mdb"

    ' Open the connection
    Set Connection = New ADODB.Connection
    Cnct = "Provider=Microsoft.Jet.OLEDB.4.0; "
    Cnct = Cnct & "Data Source=" & DBFullName & ";"
    Connection.Open ConnectionString:=Cnct

    ' Create RecordSet
    Set Recordset = New ADODB.Recordset
    With Recordset
    ' Filter
    Src = "SELECT * FROM Budget WHERE Item = 'Lease' "
    Src = Src & "and Division = 'N. America'"
    .Open Source:=Src, ActiveConnection:=Connection

    ' Write the field names
    For Col = 0 To Recordset.Fields.Count - 1
    Range("A1").Offset(0, Col).Value = Recordset.Fields(Col).Name
    Next

    ' Write the recordset
    Range("A1").Offset(1, 0).CopyFromRecordset Recordset
    End With
    Set Recordset = Nothing
    Connection.Close
    Set Connection = Nothing
    End Sub

  3. #3
    New Member
    Join Date
    Mar 2002
    Posts
    38
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Sorry, but I got a little lost. I only need to open an Access Database file, not necessarily open a form. I just want to have a link that let's the user open the database with a click.

  4. #4
    New Member
    Join Date
    Mar 2002
    Posts
    38
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Here is the code that I have tried using, but it's not working for me and not real proficient in VBA:

    Dim AccApp As New Access.Application
    Dim strDB As String
    strDB = "database filename"
    AccApp.OpenCurrentDatabase (strDB)

    Thanks for any help

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •