Solved

Creating Read-only links from one MSACCESS database to another

Posted on 2004-10-04
33
424 Views
Last Modified: 2008-01-09
1. The answer must be able to accomplish goal without changing the Read/Write permissions of a source database NOR changing Read/Write in Destination database.
2. The solution must not include database security as part of the solution.

Can a linked table from one MS Access Database be made read only?

If a link can be established at the outset via VBA  or after initially creating a link to a table the old-fashioned way THEN using VBA I will award points.

Note* I do not award points for "answers" that include solutions I have already stated I do NOT want.

shoffer

0
Comment
Question by:smhoffer
  • 15
  • 6
  • 5
  • +2
33 Comments
 
LVL 5

Expert Comment

by:jmacmicking
ID: 12219749
One way I've done this (there are probably others) is create a SQL query that points to the table instead of linking the table itself.  Put the full path to the database (use brackets if necessary), a dot, and then the table name.  Verify that it works ok.  Then go into the properties for the query and change the RecordSet Type to Snapshot to make the query read-only.  SQL Example:

SELECT * FROM [C:\Test.mdb].tblTest

0
 

Author Comment

by:smhoffer
ID: 12219972
jmacmicking,

The link needs to be the equivalent to a link created when you do File>>>Get External Data>>>Link Tables.

There are ways to create links and then readjust the links for other types of databases, e.g., SQLServer, Oracle, etc.

My need is to create a read-only link on a linked table to another Access database.

I will add:
#3  No SQL solutions

shoffer
0
 
LVL 5

Accepted Solution

by:
jmacmicking earned 250 total points
ID: 12220154
For most purposes a saved query in Access is functionally equivalent to a table. Start a new query in Design view (just like you would any other Access query), use the View menu to switch to SQL view and then put in the SELECT statement.  Make the RecordSet Type change and then save the query.  You can then use the query with virtually anything in Access that accepts a table.  You can base forms and reports off it, use it as Row Source for controls and include it in other queries.
0
 

Author Comment

by:smhoffer
ID: 12220292
As stated in my previous response my needs are for a linked table.

See #3 - No SQL Solutions

shoffer

0
 
LVL 41

Expert Comment

by:shanesuebsahakarn
ID: 12220858
Check out the DoCmd.TransferDatabase command in the Access help file. That should do what you need.
0
 

Author Comment

by:smhoffer
ID: 12221194
shanesuebsahakarn:
DoCmd.TransferDatabase does not address read-only link.

If you can find a working example which establishes a read-only link and I can verify that it works, then that would be a workable solution.
0
 
LVL 41

Expert Comment

by:shanesuebsahakarn
ID: 12221275
How is your front end secured? With Access security or through some other means?
0
 

Author Comment

by:smhoffer
ID: 12221311
1. The answer must be able to accomplish goal without changing the Read/Write permissions of a source database NOR changing Read/Write in Destination database.
2. The solution must not include database security as part of the solution.
See #3 - No SQL Solutions

No security on database and solution cannot make use of database security as per #2 criteria.

I am am looking for a solution that might not exist; this is a difficult question that is why I offer the 500 points.

shoffer
0
 
LVL 41

Expert Comment

by:shanesuebsahakarn
ID: 12221536
Without including Access security, you *cannot* make a table read only unless the source database is itself read-only, I'm afraid.

While you *could* alter permissions for the default admin user (which is what you log on as if you have no security enabled), this is *very* dangerous indeed as you will risk locking yourself out from your database(s).
0
 

Author Comment

by:smhoffer
ID: 12238925
And this is why I am looking into a link.

If I create a UDL link, one option is to make the database read-only:

[oledb]
; Everything after this line is an OLE DB initstring
Provider=Microsoft.Jet.OLEDB.4.0;Data Source=My.mdb;Mode=Read;Persist Security Info=False

This made me wonder if a similar link can be established then establish a link to tables which inherit the read-only designation of the OLE DB initstring.


0
 
LVL 41

Assisted Solution

by:shanesuebsahakarn
shanesuebsahakarn earned 250 total points
ID: 12239070
Hmm - you could use the CreateTableDef method in that case:

Dim tdf As DAO.TableDef

Set tdf=CurrentDb.CreateTableDef("MyTable",,,"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=My.mdb;Mode=Read;Persist Security Info=False")
CurrentDb.TableDefs.Append tdf
0
 

Author Comment

by:smhoffer
ID: 12240914
Can you direct me how to solve the resulting error:

No field defined — cannot append TableDef or Index. (Error 3264)
You cannot append a TableDef until you define one or more fields. Use the CreateField method to create fields, append them to the Fields collection of your TableDef object, and then append the TableDef object to the TableDefs collection.

: Keep in mind I am trying to create and retain a linked table, NOT a link to data that must be triggered by code each time it is needed.
0
 

Author Comment

by:smhoffer
ID: 12906782
Why did you give these folks assisted answers?

One answer goes against my "No SQL " rule.

The other gives me something that did not work.

No points should be rewarded in my estimation.

Merry Christmas
shoffer
0
 

Author Comment

by:smhoffer
ID: 12908383
"Can a linked table from one MS Access Database be made read only?"

"If a link can be established at the outset via VBA  or after initially creating a link to a table the old-fashioned way THEN using VBA I will award points."

The above criteria was part of the comment and SQL does not gel with it. I added #3 to emphasize the establishment of a linked table.

I gave  hanesuebsahakarn a chance to give proof of concept in the use of DAO.TableDef and would have awarded points, but there was no followup.

I cannot control what your decisions are AND I did not get to this until too late, but in my judgement shanesuebsahakarn was on the right track.

Even my title "Creating Read-only links from one MSACCESS database to another" is very clear that I am talking about links, not referencing or querying against tables. I cannot agree that the first comment answers my question based on my title or the two quoted sections above."

shoffer
0
Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

 
LVL 50

Expert Comment

by:Steve Bink
ID: 12912803
Just a couple more pennies in the jar:

Your requirements sort of contradict each other.  The ability to access or modify data in a table is managed by security, in one form or another.  Whether it is security on your back-end, or Access security on a linked table in the front-end, security MUST enter into the equation SOMEWHERE.  Since you're first caveat rules out changing any security values on either the source or destination databases, you are essentially looking for a solution from the "middle-man"...ODBC, OLEDB, etc.  In that sense, Shane's solution is the only workable solution you could possibly accept.  The implementation of "air-code" usually does not work from a copy/paste, and needs a little fine-tuning, but we can always expand on the basic idea to arrive at something workable.  I've never had to create a read-only link by manipulating the TableDef object, so I cannot really verify if it will work as advertised.

The moderator still has a good point, though: the first solution works.  Regardless of how you would LIKE to implement it, the fact stands that the "SQL solution" will work exactly the same as a linked-table solution, and a saved query can be manipulated just as easily as a TableDef.  I would not even call it a true SQL solution, since Access will still use the built-in DAO engine to create and execute the link (SQL is data definition and manipulation, not a comm manager).  The only difference between the solutions offered by Shane and jmacmicking is that Shane is creating a table, and jmac is creating a query.  Both are FUNCTIONALLY equivalent, and do not require any particular security.  

Out of curiosity, can you explain why you have these special requirements regarding the solution?  I can understand if database security is not under your control...a common problem for developers.  Not so common to restrict yourself to only certain developmental paths, some of which may not be in your best interests.
0
 

Author Comment

by:smhoffer
ID: 12913922
Modulo: I completely agree that I missed the 4 day opportunity. There is no animosity or argument with that.

routinet: MY question was supposed to find a specific type of solution not a general equivalent. I have coded the ability to re-link to SQL Server tables DSN-less for clean access and management reasons.

I made a feeble attempt at explaining clearly that I did not want a solution that "will work exactly the same as a linked-table solution." I wanted a solution that was stated as "Can a linked table from one MS Access Database be made read only?"

Maybe the answer is "no." I have noticed over the past year or so that many people answering questions are so desperate for points that they will give all sorts of easy answers and answers that are contrary to the question, i.e., "Your database is corrupt, create a new one import the data and it will work."

There are too many cases of the answer equivalent of CTL-ALT-DELETE when there is another solution.

In Access an Access link may follow the following format:
DATABASE=Y:\database\MyData.mdb;TABLE=tbl_data

In Access a link to SQLServer:
ODBC;DRIVER=SQL Server;SERVER=SQLSERVER1\PRD1;APP=Microsoft Office 2003;WSID=6VCTF21;DATABASE=MYCMT_REPORT;Trusted_Connection=Yes;TABLE=dbo.users

An UDL connection appears to limit the connection to Read only:
Provider=Microsoft.Jet.OLEDB.4.0;Data Source=J:\PD-StrPl\Database\SqFtgReport\Target\tgt_sq_ftg_LiveData.mdb;Mode=Read;Persist Security Info=False

My question was intended to find a method of using code to create the equivalent of the UDL link [which appears to allow the setting of ReadOnly] or of the type of ODBC link to SQLSERVER [which used code to create the link]

I tried to be as clear as possible that I am searching for a specific type of solution...manipulating a link was the question not how to create a query.
~~~~~~~~~~
Can a linked table from one MS Access Database be made read only?

If a link can be established at the outset via VBA  or after initially creating a link to a table the old-fashioned way THEN using VBA I will award points.
~~~~~~~~~~~~~~~~~

When did Experts-Exchange become a place where "Regardless of how you would LIKE to implement it, the fact stands that the "SQL solution" will work exactly the same as a linked-table solution, and a saved query can be manipulated just as easily as a TableDef" is the criteria for a correct or accepted answer?

"Hey buddy, I ordered an Audi?"

"So what?... a KIA will get you where you want to go regardless of what you wanted drive."

shoffer
0
 
LVL 50

Expert Comment

by:Steve Bink
ID: 12914370
It is not that I am trying to influence you into a bait-and-switch, so to speak, but programming problems are rarely as simplistic as you imply.  As a programmer, I ALWAYS take the easiest route available, so long as it does not conflict with other portions of the project.  There have been many occasions when I've used an easy solution, only to go back and change it later because of additional project constraints that made it unworkable.  But there are many times when the simple solution not only works, it is the best bet.  Limiting yourself to a particular strategy or method does just that...limits you.  

As I said before, the security limitations are more than understandable - they are pretty much a given these days.  Developers do not often control the security for the network on which their final copies are placed.  I can even partially understand your "No SQL" requirement, since you may need to do this programmatically.  In that sense, either of the solutions from jmac or Shane would still have been suitable.

As far as EE and the solutions provided by experts, sometimes you just can't do it.  As you noted, "It can't be done" is an acceptable solution for particular questions.  However, not ALL of us are willing to accept that answer, and I will usually try to give alternative, functionally equivalent methods.  In that spirit, yes, they are acceptable solutions, and functional equivalency is a perfectly acceptable criteria for resolving ANY issue.  Your comparison to leasing a car does not stand because the car you drive is not necessarily functionally equivalent to the car you ordered (Kia vs Audi).  If a user can see no difference in the interface between one coding approach and another, how does the particular implementation affect the project?  It doesn't.  Your comparison would be more suitable if I ordered a car, and they sent a competitor's brand with the same technical and structural specifications as the original.  Better yet, does it matter if I buy Tylenol(TM) or generic?  If I'm concerned about the brand, then yes.  Users are not often concerned about how a db is programmed, just if it works or not.

In any case, it is a moot point.  Despite any restrictions you placed on the solution, Shane's meets the criteria (once it is 'fine-tuned').  If you choose not to use Shane's idea, jmac's is available as well.  If you decide you cannot or will not use either, then the solution is probably "can't be done".  If you'd like to explore this further, I'd be more than happy to experiment with you, as would other experts, I imagine...I can't speak for them, but I do have a good imagination.  And after all, a question left unanswered is knowledge left unlearned.  If not, I wish you good luck with the rest of your project.
0
 
LVL 50

Expert Comment

by:Steve Bink
ID: 12914403
Almost forgot...I came across these in the MSKB.  They both say pretty much the same thing.  Perhaps they will lead you to an acceptable resolution:

http://support.microsoft.com/default.aspx?scid=kb;en-us;275484
http://support.microsoft.com/default.aspx?scid=kb;en-us;195475
0
 

Author Comment

by:smhoffer
ID: 12914584
Thanks routinet.

Key point is that the solution I am searching for is NOT based on an any solution to please end users, it is a programmatic question that was graded as difficult with highest points paid out for a specific linking solution as per my request and elaborations. EE questioners should have the right to ask for a specific solution and be able to reject functional equivalency if so stated.

My interest was based on the user's satisfaction and them not knowing the difference.

The analogy stands but change it to the idea of engineers or auto enthusiasts who care about the intricate details and want to push things beyond common place. You made my point...you say a functionally equivalent method is acceptable for a database/coding question, but NOT for and Audi vs. Kia...the general functionality is equivalent [as is a linked table versus a query to said table]. Both vehicles are drivable and get you to your destination...they are functionally equivalent...but they are not necessarily performance equivalent! and this is a valid and difficult arena within database management.
Do I use an API call to perform a function or do I use a built in one. Do I nest my forms or link forms. Do I use Select Statements or If..Then....

If I asked for an API call to perform a search function and someone said, "Here is a function that builds a string of all data and returns a result..." Would that be acceptable because it performs the function in spite of the requirement that it be an API call?

I am interested in testing ALL avenues available.  My edumacated guess is that there is a solution that meets my requirements, but it is not a well-known one.

IF you find a solution, I will give you 500 points. We will have to figure out how to set up the situation if you can. If I find the solution I will post it.

I am not intending to imply any simplistic solution but am adamant about users of EE to ask questions and be able to require or exclude certain types of solutions.

I missed the deadline to respond and lost my points, but still find it important to get the point across...neither person answered the question as posed in an acceptable manner. They got a free ride.

0
 

Author Comment

by:smhoffer
ID: 12914601
routinet...let me check out the links and if they [or a derivative] work...I willl give you the 500 points...we will have to figure out how to set something up so I can ensure you are awarded them.
I hope to have time to try it  by tomorrow.

Thanks,
shoffer
0
 
LVL 50

Expert Comment

by:Steve Bink
ID: 12914682
I do understand your point...you should be able to receive the answer you are looking for, not the one someone else thinks you are looking for.  When speaking solely about hypotheticals (i.e., CAN this be done vs how do I do it) I would even agree.  I just feel that implementation is better served by expanding the horizons, not constricting them.  We can agree to disagree on this point.  :)  I also know exactly what you're talking about regarding the 'easy-out' answers, and find them a little shameful, myself.  Some people just don't care enough to read the whole question, I guess.

The links I posted contained the only information of value I found during my search of the KB.  They both state that using a connection-level read-only constant when creating the connection is an acceptable method.  I have not tried this, but if MS says it is so, it must be so, right?  <smirk>

I still think Shane's solution deserves some refining, but I have nothing around me with which to test it.  I can try testing later tonite using MSSQL2k and Acc03.  BTW, which version(s) are you working with?
0
 
LVL 50

Expert Comment

by:Steve Bink
ID: 12914685
Don't worry about the points...I have more than enough, and you've already paid once for a solution.  :)
0
 

Author Comment

by:smhoffer
ID: 12915100
It is ACC2003.

If you change your mind I am still good for the points [if it works...]

Oh yes...If MS say its so then it is so and so....

I will let you know. Thanks for conversing on the subject, it is appreciated...

shoffer
0
 
LVL 27

Expert Comment

by:jjafferr
ID: 12915421
Thats the spirit guys,
I have subscribed in this question from day one, and it seems I will not be disappointed, as I am interested in the technique too.

Thanks guys,

and routinet, you are doing one hell of a job cleaning up, heads down to you.

jaffer
0
 
LVL 41

Expert Comment

by:shanesuebsahakarn
ID: 12920096
This should do it:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/office97/html/connectingtoserver.asp

Replacing strConnect with your UDL connection string should do it, but I have not tested it.
0
 

Author Comment

by:smhoffer
ID: 12920514
Ahhhh...This looks exactly like what I need.

The version routinet links may also work.

Both will be tested this afternoon...my guestimate is that both methods will result in a LINKED table to be made readonly.

I will post when testing is complete.

anticipatorily yours...
shoffer
0
 
LVL 50

Expert Comment

by:Steve Bink
ID: 12920697
I did not test the UDL, but I've been trying to link the table through code all morning with little success.  Well, success in linking, but not with read-only status.  I did find some other information here:

http://support.microsoft.com/default.aspx?scid=kb;en-us;292783

This is a registry fix to force Access to use snapshot-style links for external data, which means the linked table will be read-only.  This could be problematic, since you may have other tables you do not want to be read-only, and I BELIEVE this sets the default behavior for Access as a whole.  I have not really looked into it much...

Most of the trouble I'm running into deals with Access not having an installable ISAM for itself.  The ISAM header that can be used for, say, Excel or text documents, is supposed to support a read-only mode.  I read that somewhere this morning, but I'll be damned if I can find that page again...should have bookmarked it.  Oh well.  In any case, Access is not listed for ISAM use in the registry, and my attempt at a fix (by using the other ISAM entries as a template) did not have any effect.

Let us know what you find out about the UDL...I'll keep playing.
0
 

Author Comment

by:smhoffer
ID: 12927210
Nothing yet...will try again today
0
 

Author Comment

by:smhoffer
ID: 13015822
Minor tests, but no success...continuing..
0

Featured Post

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

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…
In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

708 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

17 Experts available now in Live!

Get 1:1 Help Now