Copying an Access recordset into Excel

[brans]

I am trying to copy an access recordset using a macro in excel. This works on some machines and on others it gives a runtime error. We thought the problem was an Office 2K compatability issue, but we recently ran across an Office 97 machine that has the same issue.

Here is the code:

Sub CopyRecordset()
Dim Db1 As Database
Dim Rs1 As Recordset

Worksheets("CalData&quot.Select
Range("A2:E1000&quot.Select
Selection.ClearContents
Range("A2&quot.Select

Set Db1 = DBEngine.OpenDatabase("C:\Salaried Calendar\Salaried Vacation-Branch - 97.mdb&quot

Set Rs1 = Db1.OpenRecordset(Name:="VacQuery", Type:=dbOpenDynaset)

With Worksheets("CalData&quot.Range("A2&quot
.CopyFromRecordset Rs1
End With
Db1.Close

End Sub

The error occurs at Set Db1= DBEngine... saying it can't find the database (it is definitely there and spelled correctly). If we change the: "Dim Db1 as Database" to Databases it finds the database but gives a compile error "Method or data member not found" at OpenRecordset.

I can run this code on my Office 97 machine perfectly, but it does not run on my Office 2000 machine (nor anyone else's we've found).

Is there a different/easier way to accomplish copying this recordset from a closed access database into an excel spreadsheet?

 

[DaleWatson123321]

Hi brans,

As a workaround, might I suggest you consider running your "VacQuery" from Access each time a user exits from the Access form where changes could take place, and exporting the data to a separate Excel file. Then have your Excel macro pull the data from that separate Excel file.

A couple of weeks ago, I responded to the following question by "smarque1". Because the answer I supplied is also relevant to your question, I have copied the question and my answer, for your reference.

I hope this helps.

...Regards, ...Dale Watson
nd.watson@home.com -or- dwatson@bsi.gov.mb.ca

------------------------------
Question from "smarque1"
------------------------------
I'm trying to create a drop down list from a table in an access database. I want the specified cell to reference data in the remote table (last name) and display it as a drop down list for selection. Can this be done? If so How?

- Thanks for your time

------------------------------------
My Response - June 26, 2001
------------------------------------
Hi smarque1,

I'm not aware of a method of referencing data in an Access table directly. However, I have set up an application whereby I do update Excel dropdown lists whenever a change is made to an Access database.

I accomplished this by exporting the required data to a "common" file each time an Access user exits from the input form. The code is (On Close):

DoCmd.TransferSpreadsheet acExport, 8, "Contact_List_Qry",
"C:\path\filename.xls", True, ""

Then on the Excel side, the data is automatically imported whenever the user opens the file (and the dropdown lists updated). This is the code:

Dim FirstCell As String
Dim LastCell As String
Dimm LastRow As String
Dim LastColumn As String

Sub Workbook_Open ()
Application.ScreenUpdating = False
Workbooks.Open FileName:="C:\path\filename.xls"
Range("A1:AZ1000&quot.Select
Selection.Copy
ActiveWindow.ActivateNext
Sheets("BSI_Contacts&quot.Select
Range("A20&quot.Select
ActiveSheet.Paste
Application.DisplayAlerts = False
Workbooks("Contact_Data.xls&quot.Close SaveChanges:=False
Application.DisplayAlerts = True
Sheets("BSI_Contacts&quot.Select
Set_Data_Range
Update_Lists
End Sub

Sub Set_Data_Range ()
Range("data&quot.Select
FirstCell = ActiveCell.Address
Get_Last_Row
LastColumn = "I"
LastCell = LastColumn & LastRow
Data_Range = FirstCell & " : " & LastCell
Range(Data_Range).Name = "Data"
End Sub

Sub Get_Last_Row()
ActiveCell.SpecialCells(xlLastCell).Select
ActiveCell.Offset(3, 0).Select
ActiveCell.End(xlToLeft).Select
ActiveCell.End(xlUp).Select
LastRow = ActiveCell.Row
End Sub

Sub Update_Lists
'Two separate lists are updated from the imported data
Range("data&quot.AdvancedFilter Action:=xlFilterCopy, _
CriteriaRange:="release_all", _
CopyToRange:=Range("rel_out&quot, _
Unique:=True

Range("data&quot.AdvancedFilter Action:=xlFilterCopy, _
CriteriaRange:="location_all", _
CopyToRange:=Range("loc_out&quot, _
Unique:=True
End Sub
--------------------------------------------------------------
That's it !! ...it works for me ...hope it works for you !!

Regards, ...Dale Watson
--------------------------------------------------------------