473,326 Members | 2,182 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,326 software developers and data experts.

Get data from Access form into Excel using ADO

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_Open" event.

Expand|Select|Wrap|Line Numbers
  1. Private Const DatabasePath As String = "C:\Documents and Settings\Owner\My Documents\Storm Shield Database.mdb"
  2. Dim strEstimateID As String
  3.  
  4.  
  5. Sub TransferFromDatabase()
  6.     Dim AdConnection As ADODB.Connection, Estimate As ADODB.Recordset
  7.  
  8.     ' Connect to the Access database
  9.     AdConnection.Open "Provider=Microsoft.Jet.OLEDB.4.0;" &_
  10.     "Data Source= " & DatabasePath & "; Persist Security Info=False"
  11.  
  12.     ' Open a recordset
  13.     Estimate.Open "qryEstimates", AdConnection
  14.  
  15.     ' Find the current estimate based on the EstimateID
  16.     Estimate.Find "EstimateID = " & strEstimateID
  17.  
  18.     ' Add a new estimate if an existing one isn't found
  19.     If EOF = True Then Estimate.AddNew
  20.  
  21.     ' Fill the appropriate cells with data from the database
  22.     ActiveSheet.Cells(I6) = Estimate.Fields(EstimateID).Value
  23.      *
  24.      *
  25.      *
  26.  
  27.     ' Close the recordset and connection
  28.     Estimate.Close
  29.     AdConnection.Close
  30.     Set Estimate = Nothing
  31.     Set AdConnection = Nothing
  32. End Sub
  33.  
As you can see in line 15, I'm trying to use the variable strEstimateID as the criteria in the "Estimate.Find" 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...

Expand|Select|Wrap|Line Numbers
  1. Private Sub btnOpenEstimate_Click()
  2.  
  3.     ' Set path and filename of the estimate sheet to open
  4.     strEstimateSheetPath = "I:\Miscellaneous\Database Files\Estimate Sheets\"
  5.     strEstimateSheetName = ProjectName & " -" & Str(EstimateID) & ".xls"
  6.  
  7.     ' If new record: open new estimate sheet -- else: open corresponding estimate sheet
  8.     If IsNull(EstimateID) Then
  9.          Call ShellExecute(0, "", strEstimateSheetPath & "Estimate Sheet.xls", "", "", 2)
  10.     Else
  11.          Call ShellExecute(0, "", strEstimateSheetPath & strEstimateSheetName, "", "", 2)
  12.     End If
  13. 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.
Jul 20 '08 #1
7 13125
ADezii
8,834 Expert 8TB
  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.
Jul 20 '08 #2
  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....
Jul 21 '08 #3
ADezii
8,834 Expert 8TB
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.
Jul 21 '08 #4
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.
Jul 21 '08 #5
ADezii
8,834 Expert 8TB
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
    Expand|Select|Wrap|Line Numbers
    1. Dim intEmpID As Integer
    2.  
    3. intEmpID = Me![EmployeeID]
    4.  
    5. 'Save the value of the Employee's ID (Primary Key) in the [EmployeeID]
    6. 'Field of the Current Form to the Registry.
    7. SaveSetting appname:="Variable Transfer Demo", Section:="Employee Data", _
    8.             Key:="Identifier", setting:=intEmpID
  2. Within the Open() Event of a Workbook in Excel
    Expand|Select|Wrap|Line Numbers
    1. Private Sub Workbook_Open()
    2. Dim intEmployeeID As Integer
    3.  
    4. 'Retrieve the Employee ID from the Workbook Open() Event in Excel for
    5. 'the previously saved value in Access, if not found return 99999
    6. intEmployeeID = GetSetting(appname:="Variable Transfer Demo", Section:="Employee Data", _
    7.                            Key:="Identifier", Default:=99999)
    8.  
    9. 'Test the retrieval
    10. Debug.Print "The value of the EmployeeID retrieved in Excel is: " & intEmployeeID
    11.  
    12. 'Assign the Employee ID to Cell $F$10 on Sheet1
    13. Worksheets("Sheet1").Cells(10, 6) = intEmployeeID
    14. 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.
Jul 21 '08 #6
  1. Within Employee Form in Access
    Expand|Select|Wrap|Line Numbers
    1. Dim intEmpID As Integer
    2.  
    3. intEmpID = Me![EmployeeID]
    4.  
    5. 'Save the value of the Employee's ID (Primary Key) in the [EmployeeID]
    6. 'Field of the Current Form to the Registry.
    7. SaveSetting appname:="Variable Transfer Demo", Section:="Employee Data", _
    8.             Key:="Identifier", setting:=intEmpID
  2. Within the Open() Event of a Workbook in Excel
    Expand|Select|Wrap|Line Numbers
    1. Private Sub Workbook_Open()
    2. Dim intEmployeeID As Integer
    3.  
    4. 'Retrieve the Employee ID from the Workbook Open() Event in Excel for
    5. 'the previously saved value in Access, if not found return 99999
    6. intEmployeeID = GetSetting(appname:="Variable Transfer Demo", Section:="Employee Data", _
    7.                            Key:="Identifier", Default:=99999)
    8.  
    9. 'Test the retrieval
    10. Debug.Print "The value of the EmployeeID retrieved in Excel is: " & intEmployeeID
    11.  
    12. 'Assign the Employee ID to Cell $F$10 on Sheet1
    13. Worksheets("Sheet1").Cells(10, 6) = intEmployeeID
    14. 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.
Jul 22 '08 #7
ADezii
8,834 Expert 8TB
That worked excellently! Thank you so much!

That's a great tool. I'll use that a lot.
You are quite welcome, enjoy.
Jul 22 '08 #8

Sign in to post your reply or Sign up for a free account.

Similar topics

2
by: Niyazi | last post by:
Hi, I have to retrieve a data from AS400 DB2 and after working with data I have to export into one of existing Excel file. I can connect into specific library in AS400 DB2 using AS400...
4
by: Martin | last post by:
There is an Access table on the network. 15 users who do not have Access are connected to the network. Is there a way for each user to be able to enter one or more rows containing 3 or 4 columns to...
8
by: Colleyville Alan | last post by:
I have been working on an Access app that takes info from a file and writes it to a spreadsheet on a form, simultaneously saving the spreadsheet to Excel. I got the idea that the same concept...
7
by: Darren | last post by:
I have been attempting to create a reservation planning form in excel that imports Data from an Access database and inserts that information automaticly into the correct spreed sheet and the...
5
by: Jonny | last post by:
Hello, I have created a button on my form which when pressed does the following : 1) Run a pre-defined Macro, which is picking up a query and running my data to excel. However, I need the...
2
by: JM | last post by:
Hi I have created a Windows Form that takes input. When the Run button is pressed the form is disabled and the code checks some files and inputs the data into an Excel worksheet (that is hidden...
1
by: Herbert Chan | last post by:
Hello, I'm subscribing to funds on HSBC, and the fund prices can be found here: http://www.tools.hsbc.com.hk/search/mpf?id=hk+mpf+fundsearch&lang=en&init=true At the moment, I'm copying each...
7
by: semijoyful | last post by:
OS: Win XP SP2 Access version: 2003 Excel version: 2003 I am new at this, as I am sure you have gathered from this post title:) I am working on a form where users can input data in Access and...
2
by: eko99312 | last post by:
Dear All, As you know that input data on excel is easy than access, you can simple click the date that you prefer and input the value that you want. For example : Consider that the month that I...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.