smhoffer
asked on
Creating Read-only links from one MSACCESS database to another
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
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
ASKER
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
As stated in my previous response my needs are for a linked table.
See #3 - No SQL Solutions
shoffer
See #3 - No SQL Solutions
shoffer
Check out the DoCmd.TransferDatabase command in the Access help file. That should do what you need.
ASKER
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.
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.
How is your front end secured? With Access security or through some other means?
ASKER
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
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
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).
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).
ASKER
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.OLE
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.
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.OLE
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.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
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.
ASKER
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
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
ASKER
"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
"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
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.
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.
ASKER
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\MyDat
In Access a link to SQLServer:
ODBC;DRIVER=SQL Server;SERVER=SQLSERVER1\P
An UDL connection appears to limit the connection to Read only:
Provider=Microsoft.Jet.OLE
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
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\MyDat
In Access a link to SQLServer:
ODBC;DRIVER=SQL Server;SERVER=SQLSERVER1\P
An UDL connection appears to limit the connection to Read only:
Provider=Microsoft.Jet.OLE
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
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.
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.
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
http://support.microsoft.com/default.aspx?scid=kb;en-us;275484
http://support.microsoft.com/default.aspx?scid=kb;en-us;195475
ASKER
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.
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.
ASKER
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
I hope to have time to try it by tomorrow.
Thanks,
shoffer
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?
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?
Don't worry about the points...I have more than enough, and you've already paid once for a solution. :)
ASKER
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
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
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
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
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.
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.
ASKER
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
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
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.
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.
ASKER
Nothing yet...will try again today
ASKER
Minor tests, but no success...continuing..
SELECT * FROM [C:\Test.mdb].tblTest