0

I'm trying to create a link from an Access Query to a specific Excel-Sheet.

However when I run the code, I get an Run-time error 424 Object required, debugging shows that fileToOpen does show the correct database but when I mouse over acQuery it says acQuery = Empty.

I've checked the query name a couple of times and it should all be correct.

Public Function ImportAccess()

Dim fileToOpen As Variant
Dim fileFilterPattern As String

fileFilterPattern = "Access Files (*.mdb*),*mdb*"

fileToOpen = Application.GetOpenFilename(fileFilterPattern)

If fileToOpen = False Then
        ' Input cancelled
MsgBox "No file selected."
Else

DoCmd.TransferDatabase acLink, "Microsoft Access", fileToOpen, acQuery, "Query", "ExcelSheet", True

End If

End Function

In Excel we run calculations on pipediameters and flows based on the information that is created in the Access query. Basically I want the Access Data shown on the "ExcelSheet" sheet, so I can run calculations in Excel on other sheets in the workbook (so the data from Access won't be edited in Excel). The reason that I want to link it is so that I don't have to import data each time a change is made to the AccessDatabase.

Improve this question
6
  • 2
    You don't link an Access query to spreadsheet. That's not how it works. Access links to sheet and the linked sheet is treated like a table, except cannot edit data. Then you can build a query object that uses the linked table as source. Why would you open an Access object if you want Access linking to Excel? If you want Access linking to Excel then should reference an Excel file path/name in the FileName argument. Commented Oct 11, 2021 at 9:44
  • If this code runs in Excel there is no DoCmd.TransferDatabase. To me it is not clear: do you want to see the Excel-Sheet in Access or do you want to see an Access-Table in Excel? Commented Oct 11, 2021 at 9:45
  • @Ike I would like to see my Access Query data in Excel, so that I can run som calcuations in Excel. The reason I want to Link the data, is so I don't have to reimport each time there is a change in the AccessDatabase. The Accessdatabase contains informations about Pipe diameters, slopes etc. the Excel sheet then calculates flows based on that and other information. The code is run from Excel, so maybe TransferDatabase is the wrong one to use? Commented Oct 11, 2021 at 9:52
  • 1
    If you want data transferred from Access to a standing Excel workbook, then have Excel make the connection. Using the Data tab tools is one way. Another is VBA to make connection, open recordset and run CopyFromRecordset command to place data on sheet. Commented Oct 11, 2021 at 9:52
  • @June7 That's basically what I want, but I wanted to do it just once with a single button where you just pick your database location (VBA) to help other users that aren't techsavvy and thereafter it should be a dynamic link. But that seems like it is not possible? Commented Oct 11, 2021 at 9:57

1 Answer 1

Reset to default
0

Your DoCmd Module is not documented here. Nor it's Sub/Function TransferDatabase We can assume it needs a connection to Access in order to do it's "black magic". Since we don't know where you got your code or example from, we can't look into a crystal ball and give you an answer.

What we can do is tell you where you can find information on how to connect Excel to Access: How to query a MS-Access Table from MS-Excel (2010) using VBA

Which we can only assume is the same type of connection that you need for your function.

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.