Solved

Alternative suggestions to the "You cannot use ODBC to import from, export to, or link an external Microsoft Jet ..." problem?

Posted on 2004-07-31
10
3,363 Views
Last Modified: 2008-01-09
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
      Q_20987912
      Q_20753824
      Q_20509914
      Q_20581447

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:
      http://www.mcse.ms/archive166-2003-11-77596.html

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.
      
With thanks,
      
Luke
      
      
      
0
Comment
Question by:LukeB
  • 3
  • 3
  • 2
  • +1
10 Comments
 
LVL 36

Accepted Solution

by:
SidFishes earned 275 total points
ID: 11684386
as for benefits/issues of upsizing...see

http://www.experts-exchange.com/Databases/MS_Access/Q_20999335.html
http://www.experts-exchange.com/Databases/MS_Access/Q_21077054.html

my basic theory is that if you are going to make the jump to mssql...don't jump half way. remaining with an mdb fe and odbc will probably only put off making the switch for a while. It's just not nearly as flexible/fast/easy to maintain etc

So I'll give you my thoughts on the adp solution

because you point the fe directly yo the sql server and interact with the tables directly you you do things like interact with different databases on the sql server  ie: test and production

Select * from Dev.dbo.mytable
Select * from Production.dbo.mytable

so you could write all your code to use this ability

strSQL = "Select * from" & varDSN & ".dbo.mytable"

so you could dynamically set the dsn from code and you don't need to mess with anything on distribution...this also works well on live data if you have divisions or companies with same app structure but different data

more simply you can manually change the connection from Dev back to Production before deployment ...just a single point at the table and shoot command

the other MAJOR benefit is the use of stored procedures...if you write most if not all of your sql as storred procedures...changes to the way your sql works can be done with out even touching the front end

on the front end behind  button click perhaps

EXEC rptGetSomeData

on the server

create procedure rptGetSomeData
Select * from aTable /*you can change this however you want..all the FE knows is the name of the procedure*/
go

as for adp FE changes, i do all my work connected to dev, reset the connection to production, compile to ade's (compiled same as mde) then dump the ade is a distribution directory which everyone has access to

i've written a vb exe which is installed on everyone's desktop, they start the app with this which grabs a fresh copy of the ade at startup everytime.

there's one drawback if you do offsite development...it's harder to take the BE with you can't just copy both mdbs and go.. you can however, install msde on your offsite dev machine, make a backup of the mssql be copy that and restore to your dev box...(you can even do this if the production servger is running on full mssql server...msde is functionally indentical to the full app...it is just performance throttled)

this works ONLY if you're not messing with schema and are just editing the fe. you can however make documented schema changes offsite and then change the production db when you return ...(and even tho it's never a good idea to mess with production...sql server lets you change schema with having clients stop working)

once i was over the fairly large learning curve and rearranged the way i think about development...i've found adp's the most effecient and efffective way of maintain my existing apps and developing new ones..



0
 
LVL 36

Expert Comment

by:SidFishes
ID: 11684399
err..
schema withOUT having clients stop working)
0
 
LVL 15

Expert Comment

by:will_scarlet7
ID: 11684581
My thoughts would be, based on the number of clients you program for that you make an update procedure in your FE that they can run when you send them an update. It would seem to answer your predicament if the user could run a procedure in wich they select the MDB file that you sent them containing the update and then it steps through importing all the updated features, but keeping their present linked tables.
0
 
LVL 39

Assisted Solution

by:stevbe
stevbe earned 125 total points
ID: 11687625
without going into the mapped drive/unc discussion entirely ... if your clients are using mapped drives then you can quickly mimic their setup with a bat file that simulates a drive mapping on your local machine ...

subst x: "C:\Documents and Settings\Steve\My Documents\Voyager Games"

this creates an X drive and I can develop and link to tables in a database on X: and then distribute.

Have you looked into re-linking code ... store the client's paths and then when the app opens check to see if they are linked to the correct location and if not relink the tables. You could store the table paths in a local table in the mde or even in a text file.

Steve
0
 
LVL 1

Author Comment

by:LukeB
ID: 11724733
sorry for delay on response, I've been on work trip.

Questions for a few of you.

Sid few questions:

0. "... basic theory is that if you are going to make the jump to mssql...don't jump half way .." ok, good feedback/kick in the arse!

1. Does ADP and MSDE have same security setup as MSSQLServer, i.e. you assign db permissions by way of domain user?

2. what are the programming / modules / function implications? I have a fair amount of code the uses VBA

3. I like the idea of the varDSN; where would I put that definition? In the global variables / declarations?

4.  "... then dump the ade is a distribution directory which everyone has access to ... they start the app with this which grabs a fresh copy of the ade at startup everytime ...". Does everyone have to take a copy of the ADE or can they all the same one located on the server?

5. "there's one drawback if you do offsite development...it's harder to take the BE with you .." This is also an issue for some of my users, they want to take the FE and BE with them sometimes. Suggestions (other than telling them to get a life)?

6. "this works ONLY if you're not messing with schema and .." Not clear what you are explaining here could you expand?

Will Scarlet a question:
"... if the user could run a procedure in wich they select the MDB file that you sent them containing the update and then it steps through importing all the updated features..." I don't think this would work, although it may; the update procedure would have to update the user's current MDE. The MDE won't allow this, especially replacing forms and modules?

SteveB
1. Ok, reasonable idea on the network drive, also solves problem if server01 goes down and the admim has to flip over to server02. The problem is my decision to map a UNC to drive X when they already have a X from something else and their path is not the same as mine.

2. Yes, I looked at relinking code and actually have coded up a prettly nifty alpha version relink tool. Althought is a bit of step up from the Link Manager it still requires someone to do the relink.

3. "... store the client's paths and then when the app opens check to see if they are linked to the correct location and if not relink the tables ...". Ok, but what about me? And this is not much better than Link Table Manager. But an idea.
0
Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

 
LVL 39

Expert Comment

by:stevbe
ID: 11725082
If the filepath to the linked tables is stored on the client PC, perhaps in an ini file or in the registry then you will always know where their data is and all you need to do on startup is to check the connect property of a linked table against the reg/ini to see if you need to relink. You can use a seperate reg/ini for development.

If the client deletes the reg key or ini file ... how can they expect the app to work after deleting files? and in this case all they need to do is repoint to their data.

Manual intervention would only be required after initial install and if they moved the location of the data file. This will not change no matter which solution you choose.

As for Map drive conflicts ... I use code from VBA Developer's Handbook (Ken Getz) which is very flexible.

Steve
0
 
LVL 39

Expert Comment

by:stevbe
ID: 11725103
clarification ...
" on startup is to check the connect property of a linked table against the reg/ini to see if you need to relink"

because you know the location from the reg/ini there is no manual intervention required, pass the path(s) to your relink function in which you would check to see if a path was passed in and if not then prompt the user.

Steve
0
 
LVL 15

Expert Comment

by:will_scarlet7
ID: 11802239
Is this still unresolved?
0
 
LVL 36

Expert Comment

by:SidFishes
ID: 11804018
"1. Does ADP and MSDE have same security setup as MSSQLServer, i.e. you assign db permissions by way of domain user?"

Yes...our you can use useraccounts and passwords

2. what are the programming / modules / function implications? I have a fair amount of code the uses VBA

i've got one project with 888,000 lines of vba...so you could say i've got a fair amount too ;) vba is fine...but there are differences...the biggest is ones are dealing with using views and stored procedures...remember that there's no such thing as mdb style queries in mssql/msde

3. I like the idea of the varDSN; where would I put that definition? In the global variables / declarations?

yep

4.  Does everyone have to take a copy of the ADE

that's what i do...some will say it's not necessary...but it only takes a few seconds to copy a new front end to each client so why not

5. "there's one drawback if you do offsite development...it's harder to take the BE with you .." This is also an issue for some of my users, they want to take the FE and BE with them sometimes. Suggestions (other than telling them to get a life)?

because msde is free they can install it and use it anywhere...you have every oneuse test data and can still import/export  forms in access as normal...

6. "this works ONLY if you're not messing with schema and .." Not clear what you are explaining here could you expand?

if you are working with table structure (schema) using many different copies, it can be hard to track changes...it can be done but it's important you create a strategy to make sure all developers are always working with the same structure

0
 
LVL 1

Author Comment

by:LukeB
ID: 11910354
Thank you fellows,

I've split the points mainly to the ADE solotion, in the long run I need to do this but will take me a while to learn and ensure it works with my coding and forms. I've also allocated points to the UNC solution for it gets me working with the client right now, it is simple to do the subst x: "C:\xxxxxx "

Luke
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

757 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now