Link to home
Create AccountLog in
Avatar of eagles1990
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
        .Open
    End With
   
    Call GetProductList
    Call GetOrderInfo
   
    ' Close the connection.
    cn.Close
   
    Range("A2").Select
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
    frmProducts.Show
    rs.Close
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")
            .MoveNext
        Loop
        .Close
    End With
End Sub
Avatar of danishani
danishani
Flag of United States of America image

You can try encorperate the Windows File Open Dialog with below API:
http://access.mvps.org/access/api/api0001.htm

Another approach is using the FileDialog property, see below link:
http://msdn.microsoft.com/en-us/library/aa195878%28v=office.11%29.aspx

Hope this helps,
Daniel
ASKER CERTIFIED SOLUTION
Avatar of Helen Feddema
Helen Feddema
Flag of United States of America image

Link to home
membership
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()