Import Excel data range into Access Database using a SQL Query.

[mcAvero]

Hi there I have used the the following SQL Query in Access to the following range (A5 to T1000) from Excel, to be placed into a Table in Access with Headers.

SELECT * INTO [Ref_Tracker_File]
FROM [Excel 8.0;HDR=Yes;database=\\mark\access\Program\Tracker\Orders\Tracker 2020.xlsm].[Tracker$A5:T1000];

I used this code a few year ago, now only get the following error:
"The connection for viewing your linked Microsoft Excel file was lost."

Thoughts or other SQL alternatives ?

 

[Joe4]

Why not just link the Excel data into an Access table?
See: Import or link to data in an Excel workbook

 

[mcAvero]

Why not just link the Excel data into an Access table?
See: Import or link to data in an Excel workbook

This would be fine if my headers in the Excel file where located in Row A1, the issue is the Headers are in Row A4, I am looking to import the data starting in Row A5.
- Not sure of an alternative otherwise

 

[Joe4]

You can still do it using Linked tables.
The key is to use named ranges in Excel. Just start the named range on row 4. Then when you are linking the data in Access, you can select the named range.

 

[mcAvero]

would agree, though I have 10 excel files from different teammates, which the code above above allowed me to bring in a selected data range.

 

[Joe4]

Well, you asked for thoughts, and that was mine.
If it were me, I would probably look to do one of the following:
1. See if the entire process can be done in Access, and get rid of the Excel part
- or -
2. Run an Excel macro that first deletes the first 4 rows of data, so the header is one row 1, before importing.

 

[mcAvero]

I thank you for the feedback, i will try option 2.