Get data from Access form into Excel using ADO

[kpresidente]

Hello all,

I'm trying to transfer the value of a control on an Access form to an Excel worksheet using VBA. The Access form is a single form with all the controls disabled, so that data is "read only." I have a button on the form which opens the Excel worksheet, which then basically serves as the input form for the Access database.

What I need is to somehow get the primary key (EstimateID) of the current record on the Access form transferred to the Excel worksheet, then use ADO and ".find" to locate that record in the Access form's query and gather the rest of the data.

Right now my code is in an Excel module that I run in the "Workbook_O pen" event.

Private Const DatabasePath As String = "C:\Documents and Settings\Owner\My Documents\Storm Shield Database.mdb"
Dim strEstimateID As String


Sub TransferFromDatabase()
    Dim AdConnection As ADODB.Connection, Estimate As ADODB.Recordset
    
    ' Connect to the Access database
    AdConnection.Open "Provider=Microsoft.Jet.OLEDB.4.0;" &_
    "Data Source= " & DatabasePath & "; Persist Security Info=False"
    
    ' Open a recordset
    Estimate.Open "qryEstimates", AdConnection
    
    ' Find the current estimate based on the EstimateID
    Estimate.Find "EstimateID = " & strEstimateID
    
    ' Add a new estimate if an existing one isn't found
    If EOF = True Then Estimate.AddNew

    ' Fill the appropriate cells with data from the database
    ActiveSheet.Cells(I6) = Estimate.Fields(EstimateID).Value
     *
     *
     *

    ' Close the recordset and connection
    Estimate.Close
    AdConnection.Close
    Set Estimate = Nothing
    Set AdConnection = Nothing
End Sub

As you can see in line 15, I'm trying to use the variable strEstimateID as the criteria in the "Estimate.F ind" command. However, at no point do I set the value of strEstimateID, because I don't know how to get that data from the control on the Access form.

I'm sure there are errors in the code, and the code is not finished, but I can't debug it until I find a method to get the primary key transferred into Excel. If you spot error, feel free to point them out, but my focus is really on the problem of the primary key.



...Not sure if it's relevant, but here is the code for the Access button which opens the Excel worksheet...

Private Sub btnOpenEstimate_Click()
    
    ' Set path and filename of the estimate sheet to open
    strEstimateSheetPath = "I:\Miscellaneous\Database Files\Estimate Sheets\"
    strEstimateSheetName = ProjectName & " -" & Str(EstimateID) & ".xls"
        
    ' If new record: open new estimate sheet -- else: open corresponding estimate sheet
    If IsNull(EstimateID) Then
         Call ShellExecute(0, "", strEstimateSheetPath & "Estimate Sheet.xls", "", "", 2)
    Else
         Call ShellExecute(0, "", strEstimateSheetPath & strEstimateSheetName, "", "", 2)
    End If
End Sub

I'm not a programmer (well...2 years of Basic in HS :-) ), so keep that in mind in your answers.

P.S. - I could probably use the clipboard to transfer the value of the control on the Access form, but I want to avoid that if possible.

[ADezii]

1. Link the Excel Worksheet to the Current Database, now it can be treated as any Local Table would be.
2. Create a Form and set its Record Source equal to the Linked Worksheet.
3. Updating the Form will update the Worksheet, and you can now easily transfer values between Forms.

[kpresidente]

1. Link the Excel Worksheet to the Current Database, now it can be treated as any Local Table would be.
2. Create a Form and set its Record Source equal to the Linked Worksheet.
3. Updating the Form will update the Worksheet, and you can now easily transfer values between Forms.

Thanks ADezii, but unfortunately I can't do that. Each record in the Access table is associated with a seperate excel workbook, saved under a unique filename, but having the same structure (basically a template).

What I'm trying to do is similar to passing an argument from one access form to another, only for me, the second "form" is really an Excel workbook.

It's a poor use of Excel, but I have reasons for doing it this way.

Right now I can only think of two ways of going about it:

1. Pass the variable through a text file, but I imagine that would be very slow. I'd have to open the file from access -> pass the variable -> save the file-> close it -> open it from Excel -> get the variable -> close the file. That seems like a lot.

1. Pass the variable through the clipboard, which would probably be fast enough, but I don't want to be clearing the clipboard in case the user has something else on it.

So I'm looking for another way....

[ADezii]

Thanks ADezii, but unfortunately I can't do that. Each record in the Access table is associated with a seperate excel workbook, saved under a unique filename, but having the same structure (basically a template).

What I'm trying to do is similar to passing an argument from one access form to another, only for me, the second "form" is really an Excel workbook.

It's a poor use of Excel, but I have reasons for doing it this way.

Right now I can only think of two ways of going about it:

1. Pass the variable through a text file, but I imagine that would be very slow. I'd have to open the file from access -> pass the variable -> save the file-> close it -> open it from Excel -> get the variable -> close the file. That seems like a lot.

1. Pass the variable through the clipboard, which would probably be fast enough, but I don't want to be clearing the clipboard in case the user has something else on it.

So I'm looking for another way....

If it is simply a matter of passing a Variable from Access to Excel, aside from Automation code, the least cost effective and simplest approach would seem to be writing the Variable to the System Registry in Access via the SaveSetting Statement then retrieving it in Excel via the GetSetting() Function. You seem to have the other logic already figured out. Make sense to you? If you like, I can demo a simple interchange for you.

[kpresidente]

If it is simply a matter of passing a Variable from Access to Excel, aside from Automation code, the least cost effective and simplest approach would seem to be writing the Variable to the System Registry in Access via the SaveSetting Statement then retrieving it in Excel via the GetSetting() Function. You seem to have the other logic already figured out. Make sense to you? If you like, I can demo a simple interchange for you.

Maybe something explaining the syntax a little? Access' help file for SaveSetting() and GetSetting() is a little vague and I'm not very familiar with registry keys.

[ADezii]

Maybe something explaining the syntax a little? Access' help file for SaveSetting() and GetSetting() is a little vague and I'm not very familiar with registry keys.

1. Within Employee Form in Access
   

   Dim intEmpID As Integer
   
   intEmpID = Me![EmployeeID]
   
   'Save the value of the Employee's ID (Primary Key) in the [EmployeeID]
   'Field of the Current Form to the Registry.
   SaveSetting appname:="Variable Transfer Demo", Section:="Employee Data", _
               Key:="Identifier", setting:=intEmpID

2. Within the Open() Event of a Workbook in Excel
   

   Private Sub Workbook_Open()
   Dim intEmployeeID As Integer
   
   'Retrieve the Employee ID from the Workbook Open() Event in Excel for
   'the previously saved value in Access, if not found return 99999
   intEmployeeID = GetSetting(appname:="Variable Transfer Demo", Section:="Employee Data", _
                              Key:="Identifier", Default:=99999)
                              
   'Test the retrieval
   Debug.Print "The value of the EmployeeID retrieved in Excel is: " & intEmployeeID
   
   'Assign the Employee ID to Cell $F$10 on Sheet1
   Worksheets("Sheet1").Cells(10, 6) = intEmployeeID
   End Sub

3. The Key here is that both Office Components will refer to the same location in the System Registry (look up the Section/Key/Value with Regedit.exe).
4. Code has been tested and is fully operational.
5. Let me know how you make out, any questions feel free to ask.

[kpresidente]

1. Within Employee Form in Access
   

   Dim intEmpID As Integer
   
   intEmpID = Me![EmployeeID]
   
   'Save the value of the Employee's ID (Primary Key) in the [EmployeeID]
   'Field of the Current Form to the Registry.
   SaveSetting appname:="Variable Transfer Demo", Section:="Employee Data", _
               Key:="Identifier", setting:=intEmpID

2. Within the Open() Event of a Workbook in Excel
   

   Private Sub Workbook_Open()
   Dim intEmployeeID As Integer
   
   'Retrieve the Employee ID from the Workbook Open() Event in Excel for
   'the previously saved value in Access, if not found return 99999
   intEmployeeID = GetSetting(appname:="Variable Transfer Demo", Section:="Employee Data", _
                              Key:="Identifier", Default:=99999)
                              
   'Test the retrieval
   Debug.Print "The value of the EmployeeID retrieved in Excel is: " & intEmployeeID
   
   'Assign the Employee ID to Cell $F$10 on Sheet1
   Worksheets("Sheet1").Cells(10, 6) = intEmployeeID
   End Sub

3. The Key here is that both Office Components will refer to the same location in the System Registry (look up the Section/Key/Value with Regedit.exe).
4. Code has been tested and is fully operational.
5. Let me know how you make out, any questions feel free to ask.

That worked excellently! Thank you so much!

That's a great tool. I'll use that a lot.

[ADezii]

That worked excellently! Thank you so much!

That's a great tool. I'll use that a lot.

You are quite welcome, enjoy.