Can I Connect Access to Excel Data Model?

[DrBacon]

Hello all!

I am fairly new with Access, and would like to import a large data set (over 1.2M rows) I have in an excel data model. When I go to import the information from access, it does not let me connect to the workbook's data model. Is there a way to do this?

Cheers,
Bacon

 

[Micron]

If you had asked I would have said you should not be able do that, but I've never tried. One way would be to just link the spreadsheet as if it were an Access linked table. What's often done to eliminate data type issues (text being transported as numbers or vice versa for example) is to have a properly designed table (wrt data types) and you append the linked records to it. Just linking the spreadsheet should not cause your db file to be large, but 1.2 M records in an actual table could exceed Access size limits. Including overhead, Access file (and I believe table) limit is 2 GB. If your data set is too large, you would have to look at one of the sql server db's as the data store. Lastly, Excel data is often improperly laid out for use in a database, so if that's the case, the whole idea might not be worth the trouble. It depends on what you intend to do with the data once you have it in a db.

 

[DrBacon]

Yes, I am frustratingly aware of the 2GB cap, but I could just create several tables and join them with queries in access. The problem with SQL is that I am currently, not sure how to export what I have in the data model to SQL (I am using MSSMS). Is this possible?

 

[xenou]

If the "data model" means data in a worksheet then it you would just follow the usual methods for import data from Excel to Access.

 

[DrBacon]

If the "data model" means data in a worksheet then it you would just follow the usual methods for import data from Excel to Access.

No, it is not in a worksheet, it is in the data model of the workbook. It currently is 1.5M rows of data.

 

[Micron]

Unless I'm forgetting, a data model is an actual file, or at least it once was. It's like defining data as tables with relationships - much like you'd do in Access. Maybe it's a background part of a workbook now rather than a specific file but I don't have the inclincation to research it. I haven't used one since Office 2007. I've never had to use any form of sql server so I cannot comment on that question. Most I've ever had to do was link to it using ODBC and linked table manager.