I realise that this problem
"You cannot use ODBC to import from, export to, or link an external Microsoft Jet or ISAM database tables to your database."
is already discussed in EE articles
I've researched a bit more and by searching the web I have got a few better explanations than what are given on EE. Essentially they are from MVPs or MCDBAs that say 'that is the way it is, cannot do by design of JET...' and 'Because the Jet database engine is not re-entrant'. My way of thinking of it is that you could cause a circular reference. The best explanation of why it won't work (but no solutions) are:
The reason I had wanted to make an MSAccess MDB a ODBC data source is:
- I develop databases with front end (forms, reports, etc) plus back end (tables) MDBs on my local machine and later copy the combo to a server. I set up the combo using MDB to MDB linking (as previous 'solutions' in EE have already suggested).
- If I need to alter the front end db I do so on my local machine (because I am away, or is at a clients office) then I copy the front end to the server or e-mail it to the user. But then either I or the users of the server have to go through Link Table manager to alter the link reference from my local machine to their server.
There are stacks of tables (over 30, mostly lookup tables) and about 4 different MDB data sources (back end MDBs). It would be much simpler to just edit and re-point the ODBC DSN file or ODBC setup from my local machine to the server. The solutions given so far in EE, 'just use MDB-MDB linking', do not take this into account.
Most of my setups are for a fairly small number of users (<10) and the heavy security of MSSql Server is not really needed (nor its cost and administration).
So given an MSAccess ODBC data source cannot be referred to in another MDB then what is the alternative besides the MDB linking, which is not great for reasons given above? My sketchy ideas are below, what I am looking for in an answer is yeah/nay to my questions below OR an alternative solution to a MDB-MDB setup.
These ideas come in part from:http://office.microsoft.com/assistance/preview.aspx?AssetID=HA010345991033&CTT=1&Origin=EC790000701033&QueryID=b8q5z-CzH&Query=SQL+Server+2000+Personal+Edition&Scope=TC%2cHP%2cHA%2cRC%2cFX%2cES%2cEP%2cDC%2cXT
I haven't tried these ideas since I do not have MSDE2000 installed and can't for a while (on the road for 6 weeks and don't have my Office CD to install it). And I would like to get some idea of what I am up against with diving into SQL. I have a lot of MDB experience (programming and setups) but very little MSSql Server experience.
What I can think of as alternatives to a MDB front end + MDB back end setup:
1. Access Project and SQL Server Desktop Edition (MSDE2000)
- do the setup on local machine (ADP front end and the back end tables on MSDE2000)
- when ready to put on server do a Tools | DB utils | Transfer db
- if make any changes to interface do so on local machine and do another Tools | DB utils | Transfer db?
Q1a: Is this workable? Can I make changes to interface on local machine and then just plunk it on the server to overwrite the old one?
Q1b: Are they any major differences between APD-MSDE2000 compared to MDB-MDB?
Q1c: Are there any major benefits to using MSDE2000? One I can think of is ADUC authentication of users instead of having to use the Access MDB security. And up sizing later to full SQL server.
2. Access MDB (front end) and SQL Server Desktop Edition (MSDE2000) or MSSql Personal Edition
- make the tables under MSDE2000 on local machine and then use DSN ODBC file to link them to the MDB interface
- do all the setup on local machine
- when ready move all to server. Edit the DSN file and repoint to server so users reference the server copy
- if make any changes to interface do so on local machine and just copy the MDB it over to the server?
Q2a: Is this workable? Can I do as above, i.e. do later changes to the interface on local machine and just copy the MDB it over to the server? For example do the changed to interface on my local machine and e-mail it to office and have some one drop it over top of the old version?
Q2b: How do I setup the tables in MSDE2000, I understand there is no interface for MSDE2000?
Q2c: Would it be better to use MSSQL Personal Edition instead of MSDE2000 ?
Again what I am looking for in an answer, and awarding of points, is yeah/nay to my questions above OR an alternative solution to a MDB-MDB setup. I've not given it 500 points for not really urgent not 'difficult ' but rather I am expecting an answer that should be extensive, well thought out, logical and I am relying on someone else past experience with MSDE2000.