Splitting db with linked Excel table

[BigPat]

Hi,



I'm in the process of trying to split my database for security and ease of use, but I'm getting the 'subscript out of range' error. I believe it's because I have a linked table (an employee table held in Excel which gets replaced each month, thus keeping an accurate record of current employees). I've tested by removing the relationship between this table and the Access tables and then removing the Excel table completely, and that works in that the splitter completes without the subscript error, but from there I don't know how to reinstall the Excel table. When I try re-adding it to the _be the relationship window doesn't show my other tables. I've been googling for an hour but can't find the right way to do this. Can someone help please?

[CJ_London]

regret your post is not very clear.
You can't set relationships on linked tables.
You haven't explained your setup - do you have a FE/BE arrangement?
how are you getting the error? a macro? vba code? a query?
assuming you are talking VBA have you stepped through the code?
if so, what line of code?

I don't know how to reinstall the Excel table - if this is a linked table then you would link using the appropriate option under the external data tab - and you would link it in the FE

[BigPat]

Hi again. My setup is that I have one un-split database which I'd like to split for security and multiple user access. I've attached an image of the relationships - TblUsers is a linked Excel table, not contained in Access. I'm receiving the transcript out of range error when trying to split the database using the Access Database Splitter, when the linked TblUsers table is linked. As I say, if I remove the relationship and then the table, I'm able to complete the split.
You say I would link it in the FE, so does that mean that if multiple users use this database they all need copies of the linked table?

[CJ_London]

I would manually split your db

create a copy of your existing file on your network drive and call it 'whatever_be'
open the copy
delete all queries, forms, reports, modules
compact and save

if the db is currently not split and has been used by one than one user, chances are there is some corruption so I would do the following

excel file needs to be on the network, probably in the same directory as the be and available to all users
create a new database and call it 'whatever_fe'
open the new db
in the external data tab - link to the tables in 'whatever_be'
in the external data tab - link to the excel file on the network
in the external data tab - import in order all modules, queries, forms then reports from the original db - do one group at a time to be on the safe side and to identify any potential issues
compact and save

job done - distribute a copy of the new fe file to each user - subject to them all having the same drive mapping for the be file location

with regards relationships it doesn't look like you have specified any constraints - just created the join. You are also creating relationships between the wrong fields - e.g. one example in tblEE, provider should be providerID (and numeric, not text) and linked to ProviderID in tblProviders. It may be this reason you are getting errors

the above is the basics, from your design I can see a number of issues - relationships being one. Security being another - I can see the objective, (I presume a user can only see their details) but don't see how this is achieved if a user can edit the excel file.

Also not sure if it will work having multiple users linking to the same excel file. might, might not. I would be inclined to import the excel file to a table in the be and password protect the be. Security may still be an issue depending on how much you have tied down user access in the fe.

[BigPat]

Thanks, I'll give this a go. The data isn't confidential, and the security will be provided by the folder this file will live in. Only the administrators of the data (3 or four people) will have access to the folder, and I aim to provide them with FE copies.

I'd like to import tblUsers, for sure, but there's quite a lot of staff turnover each month and the admins have made it clear that they're not interested in major work in maintaining a current employees table, so this was the solution I tried. Another application will provide a monthly report of current employees, which then becomes tblUsers and grows or shrinks depending on the current staffing level.

[CJ_London]

I'd like to import tblUsers, for sure, but there's quite a lot of staff turnover each month and the admins have made it clear that they're not interested in major work in maintaining a current employees table, so this was the solution I tried. Another application will provide a monthly report of current employees, which then becomes tblUsers and grows or shrinks depending on the current staffing level.

the problem you have is if someone leaves, they disappear from the spreadsheet - so now you have a broken link to tblEE. If you imported the file you can maintain the link because what you would do is

1. add users in the file but not in the table
2. update a 'dateleft' field the table where a user is in the table, but not the file
3. if required update the table where data is different to what is in the file

there are variations of this depending on the outcome required. But perhaps that does not matter for whatever you use the db for

[BigPat]

the problem you have is if someone leaves, they disappear from the spreadsheet - so now you have a broken link to tblEE.

Yes, I want that to happen because I can then report on orphaned links - the item in TblEE is still there, but its owner (the person in TblUsers) has left. The admins can then chase down the item and reassign it to a new starter.
I'm not an Access expert at all, just a standard user, so I may be doing it all wrong. I'd love TblUsers to not be linked, but I don't know how to maintain a Users table other than this linked method. If I could write a macro I'd do it that way where it would, each month, wipe all TblUsers data then import the new data, but I don't know how to do this. (Admins cannot edit the TblUsers data, it only comes from the monthly export from the HR application that provides it)

Oh, and before I forget, thank you very much for taking the time to help me. I truly appreciate it.

[ArviLaanemets]

I'd love TblUsers to not be linked, but I don't know how to maintain a Users table other than this linked method..

The standard approach will be:
1. You have a Excel table linked into Access DB;
2. You have a Access table (in BE DB) with columns fully or partly matching with columns in linked table;
3.You write a VBA procedure, which updates Access table with data from linked table;
4. You call the update procedure from some event, or by windows Scheduled Task.
Linked table is used only to update Access table. Your forms, reports, and other procedures use only Access table as datasource!

Admins cannot edit the TblUsers data, it only comes from the monthly export from the HR application that provides it.

Such applications usually allow read data through ODBC Queries. In his case maybe you can read data from HR application directly and update a table in Access DB with read data. Then Excel table is not needed at all!

[BigPat]

The standard approach will be:
1. You have a Excel table linked into Access DB;
2. You have a Access table (in BE DB) with columns fully or partly matching with columns in linked table;
3.You write a VBA procedure, which updates Access table with data from linked table;
4. You call the update procedure from some event, or by windows Scheduled Task.
Linked table is used only to update Access table. Your forms, reports, and other procedures use only Access table as datasource!

Yes, I would certainly like to go with that approach, but I don't have the ability to write the VBA in order to do it.