Importing Data From User-Level Secure Access Db

[Luke Bailey]

I have a database that I've been building for quite some time. There are a few aspects of the database that I would like to pull over into Excel so that I can do some more advanced pivottable and other reports. My problem is that I can't get the Query to actually pull data.

First of all, I have set up specific user-level security settings on my Access database. I think this is the main thing that is causing the hangup (since I can effectively create Excel queries for databases that have not been set up for this).

This is what happens:

I go into Excel, I run "New Database Query.."
I click MS Access Database
I browse for the database I want to connect to and select it
At which point the Query Wizard comes up and says "Choose Columns"
It then lists all of my Tables with 'plus' signs next to them so that I can expand them

However, when I try to expand the table names so that I can pull fields, nothing happens. The plus turns to a minus and nothing expands.

When I try to cancel the wizard and just add tables via Microsoft Query it says that it "Can't access [the] table". Any suggestions??

[Scott Price]

What about exporting from w/in Access instead of importing from w/in Excel?

Using this or something similar:

DoCmd.TransferSpreadsheet acExport, ...

Regards,
Scott

[Luke Bailey]

I can take a table and export it to Excel (even with just the right click --> export). That transfers over just fine. The problem is that I want to create a report in Excel based off of this information that constantly updates with current information in the database without the end user having to export the data each time they want to do something.

As such, I would like to create a link from Excel to Access that can be updated each time the Excel file is opened. I can link with other database just fine, but I can't link with this Access file.

Originally I had set my Access database to a different Security file (a different <filename>.md w) so that I could have distinct user-level security passwords and permissions. I think that this may have caused the problem. However, I have since returned the active security file to the default security file for all Access files (so that it is the same as those Access files that work with the link) and still I am unable to link Excel with my database.

I have also tried to start a new database from scratch and import all of my tables, reports, queries, and VBA. But I am unable to link from Excel to the new database either (same result).

Any other suggestions for a working model? If possible, I really need to find a fix by tomorrow as I have a presentation on Wednesday and would really like to be able to present this portion as an added surprise/bonus.

Thank you very much for your time and knowledge.

~Luke

[Scott Price]

I'm not an expert on Excel/Access interaction, but I've put out a call for a couple of our experts who work extensively in this area.

In the meantime, have you tried exporting the table to Excel, then from within Access using the spreadsheet link feature? Clunky, I know, but it's all I can think of at the moment, sorry!

Regards,
Scott

[NeoPa]

Luke,

I'm not sure why you're having any difficulty here but we'll see what we can do.

You say you can link happily to various databases but this data, even when imported into a new database, doesn't show up as columns in the table within the MS Query view?
If this is accurate, then I can only imagine that there is some other pertinent information that you've left out. This is not to imply you're doing that deliberately, simply that there is a cause for this and it's not buried within what you've already said.
Try building up a database step-by-step which allows you to connect to the data from Excel. Start off with the most basic / simple setup then progress from there until you find a step doesn't work.

While that's being considered, are you aware that you can perform a lot of Excel level functions and stuff from within any Office application (including Access) simply by referencing the MS Excel libraries in your database (Tools / references from within the VBA Editor (Alt-F11 from the application)).

It's possible you will also need to manipulate a spreadsheet itself, in which case you may need to extend that using Application Automation (Directly controlling another app).

[Luke Bailey]

Ok, I have tried starting over from a blank database.

I built a table and tried linking (worked fine)
I then imported one table from my target Db...link worked
I then imported all the tables from my target Db...link worked
I then imported one Query from my target Db...link worked
I then imported all Queries from my target Db...link worked
I then imported one form from y target Db...link failed.

Error:
<title>"ODBC Microsoft Access Driver Login Failed"</title>

"The database has been placed in a state by user 'Admin' on machine 'BAILEYLCOE' that prevents it from being opened or locked."

Any idea why my forms would make it so that I can't link to the database?

I have tried it with different forms (to make sure that it wasn't just that one), same result.

Also, the same thing happens when I import any of my reports.

Thank you for your help!

~Luke

[NeoPa]

I think we may be getting somewhere here :)
Were you trying to import the form item itself from Excel?
Likewise, the report item when you tried that?

If so, then you should understand that both forms and reports are objects that work in Access but which neither store nor return datasets.

If you can still link to the tables and queries of the database then everything is working as it should be.

To get the data that is provided to a form object within your Access database you need to link to an object (table or query) which provides the same dataset.

PS. The way you passed the information in your post was perfect. Concise and specific. It was almost as good as seeing it for myself :)

[NeoPa]

The error message you received would tend to point to another problem. That implies that you were trying to make a link of some kind when the database had not been closed (since your last design change). Any time you change the design of a db the db is locked exclusively and stays that way until it is closed (or at least properly saved but I think it must be closed). Obviously this effect is not seen simply when data is changed in any of the tables. It must be a design change.

[Luke Bailey]

I think I have it working now! Thank you for your assistance.

I wasn't trying to import the form/report from Excel. I was importing it to the new Db made from scratch and then attempting to link from Excel to the new Db. Your last post was correct in that I was getting the error because I needed to close the new scratch Db first after importing before I could make the link (though this was only the case with forms and reports, which makes sense).

In summary, I believe that this was my problem:

Something I did when setting up my user-level security made it so that Excel could not link to the data in the database. My guess is that Excel tries to link via standard user permissions or something which keeps it from reading any of the tables that I reserve permissions for admins to see.

I must have messed it up somehow so that even after reverting the Db back to the standard security settings, Excel still could not link to it.

Having created a brand new database and importing all of the tables, queries, forms, reports, and macros over to the new database and closing it (to save any design changes), I could then link to the new database via Excel.

Now I will just rename the new database and use that.

The only problem with this solution is that the new database still does not have user-level security enabled on it, which I will need to enable. Hopefully, this time, I will be able to do it in such a way that Excel can still link to it. If you have any suggestions for how to do that properly, please let me know.

Thank you for your help!

~Luke

[Denburt]

I haven't used user level security in a while however when I link to data in MS Access from Excel I click Data then Import data Then import external Data I go find the DB then I usually use a query so I can structure it in Access and it will be that way in Excel. I just had to redo one of my sheets and I see the area prompting for a password so I am quite sure it can be accomplished. If I right click the mouse in the area that has my data I can go to edit query and see a complete connection string user info password etc.

[Luke Bailey]

I have found the solution to allow a Microsoft Excel Query to link to and update from a user-level secure Access Database.

Solution: During the Get External Data importation you need to tell the link where the Workgroup information file is located that has the security information for the Access database. Do this in the following way:

Data --> Import External Data --> Import Data...

Locate the database that you want to import from.

Open it.

On the "Connection s" tab, enter in the username and password for the login that you wish to use for the database.

Here is the trick:
On the "All" tab scroll down until you see Jet OLEDB:System Database. Select this item and click "Edit Value...". In the window that pops up enter that full path of the Workgroup Information File location into the "Propery Value" section. Click OK and you're done! The query will now be able to link to your data. It will just prompt you for the password of the login that you are using each time you want to update the data.

Problem Solved! :)

[NeoPa]

Nice one Luke. Thanks for feeding that one back :)