Link to home
Create AccountLog in
Avatar of eagles1990

asked on

VBA importing database questions

Hello, I have an access file containing sales information and an excel file-which i use to import the access data. Below is the code i use to import the data. However, as you can see I need the access file to be in the same folder as the excel file in order to locate the data. I would like to change the sales orders application so that it asks the user for the location and name of the databse file. Here is what i have so far:

Option Explicit

Public cn As New ADODB.Connection
Public rs As New ADODB.Recordset
Public productID As Integer
Public productName As String
Public topCell As Range

Sub Main()

Dim strName As String
Dim strName2 As String
    'InputBox(prompt[, title] [, default] [, xpos] [, ypos] [, helpfile, context])
    strName = InputBox("Enter the file name.", "NAME")
    'Exit sub if Cancel button used or no text entered
      If strName = vbNullString Then Exit Sub
    'InputBox(prompt[, title] [, default] [, xpos] [, ypos] [, helpfile, context])
    strName2 = InputBox("Enter the path.", "Path")
    'Exit sub if Cancel button used or no text entered
    If strName2 = vbNullString Then Exit Sub
    ' Delete any previous results.
    Range("B1") = ""
    Set topCell = Worksheets("Orders").Range("A3")
    With topCell
        Range(.Offset(1, 0), .Offset(1, 4).End(xlDown)).ClearContents
    End With
    ' Open connection to database.
    With cn
        .ConnectionString = "Data Source=" & strName2
        .Provider = strName
    End With
    Call GetProductList
    Call GetOrderInfo
    ' Close the connection.
End Sub

Sub GetProductList()
    Dim SQL As String
    ' Import product info and use it to populate the list box.
    ' After frmProducts is unloaded, we will know the productID
    ' and productName of the selected product.
    SQL = "SELECT ProductID, ProductName FROM Products"
    rs.Open SQL, cn
End Sub

Sub GetOrderInfo()
    Dim SQL As String
    Dim rowCount As Integer
    Range("B1") = productName
    ' Define SQL statement to get order info for selected product.
    SQL = "SELECT O.OrderID, O.OrderDate, L.QuantityOrdered, " _
        & "L.QuotedPrice, L.QuantityOrdered * L.QuotedPrice AS ExtendedPrice " _
        & "FROM Orders O INNER JOIN LineItems L ON O.OrderID = L.OrderID " _
        & "WHERE L.ProductID =" & productID & " " _
        & "ORDER BY O.OrderDate, O.OrderID"
    ' Run the query and use results to fill Orders sheet.
    With rs
        .Open SQL, cn
        rowCount = 0
        Do While Not .EOF
            rowCount = rowCount + 1
            topCell.Offset(rowCount, 0) = .Fields("OrderID")
            topCell.Offset(rowCount, 1) = .Fields("OrderDate")
            topCell.Offset(rowCount, 2) = .Fields("QuotedPrice")
            topCell.Offset(rowCount, 3) = .Fields("QuantityOrdered")
            topCell.Offset(rowCount, 4) = .Fields("ExtendedPrice")
    End With
End Sub
Avatar of danishani
Flag of United States of America image

You can try encorperate the Windows File Open Dialog with below API:

Another approach is using the FileDialog property, see below link:

Hope this helps,
Avatar of Helen Feddema
Helen Feddema
Flag of United States of America image

Link to home
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Then, in your code, you can set a strFolderPath variable to SelectFolder()