Anybody from Access development team or from MS can help me?

[Özden Irmak]

Hi,

I've to create some queries from 2 different databases (A query which joins
different tables from both databases) and those 2 databases are secured with
a workgroup file. No matter what I tried, I couldn't find a way to do it. I
have to do this via code and these are the methods that I tried :

1) Tried to link tables onto a common database. This slapped onto my face
when I found there is no option to specify username and system database
while creating the linked tables via ADOX. (Nor you can do from inside
Access
manually)

2) Tried to import the tables onto a common database with a "SELECT INTO"
query. This also slapped onto my face when I found that this new common
database does not only import the tables but their security datas where I
get "You don't have Open/Read data right." when I try to open the imported
tables via ADO. The Interesting
thing is that when I open this database with Access 2003 it does very well
browse through the tables and records that I imported without the need of
any kind of special user right !?!?!?!

3) I tried to create a common database which is joined to a system database
and this has also slapped onto my face where it wasn't working.

What on earth does jet (ADOX) does not provide me a way to do this? There is
also no documentation in anywhere on Access and WWW about this situation?

I got really mad after all my hardwork in my application and got stuck in a
stupid problem like this...

Regards,

Özden

 

[Lynn Trapp]

Here is a direct quote from Section 45 of the Security FAQ:

Although ADOX (ADO Extensions for Data Definition and Security) is supposed
to have functional parity with DAO, it falls far short when it comes to
security. Many features simply don't work, such as assigning permissions to
Access objects (forms, and reports) and creating new users. Stick with DAO
when you need to programmatically manage security. For more specific
information about these limitations, see the white paper, "Migrating from
DAO to ADO", listed in Section 47, "Additional Sources of Information."

 

[Özden Irmak]

Dear Lynn,

Thank you for your attenion...

My research shows that DAO does not provide a solution for my problem
either? Do you have any specific solution with DAO in your mind?

Thanks,

Özden

 

[Lynn Trapp]

I'm not sure there is one as long as you are creating the link with ADO.
Perhaps someone else has done it, but I never have

 

[Özden Irmak]

Lynn,

I don't have to stick with ADO, if you know a way with DAO than please let
me know?

Regards,

Özden

 

[Tim Ferguson]

I've to create some queries from 2 different databases (A query which
joins different tables from both databases) and those 2 databases are
secured with a workgroup file. No matter what I tried, I couldn't find
a way to do it. I have to do this via code and these are the methods
that I tried :

First thought: the databases will almost certainly have to be opened in
the same DAO.Workspace object, and a Workspace object can have only one
workgroup file. If I am correct these files have to be secured using the
same mdw.

Second, you can specify full access parameters using the IN parameter in
SQL, so I would go for just creating the queries as required.

SELECT something, somethingelse
FROM ATable
IN "DATABASE=server\\share\folder\onemdb.mdb;uid=eric;pw=secret;"
RIGHT JOIN BTable
IN "DATABASE=server\\share\folder\twomdb.mdb;uid=eric;pw=secret;"
ON ATable.FK = BTable.PK
WHERE ATable.ReadyToPrint = TRUE

The full syntax for the IN clause if available in help. There is also the
curly-bracket {} version that is even more flexible, and I think it
allows you to specify an open mdb object variable (but I could be
completely wrong on that one).

Third: have you thought of ditching the Access route completely, and
using something MS Query to do it?

Just a few thoughts...


Tim F

 

[Brendan Reynolds]

I had looked up the IN clause in Access 2003 help, and it makes no mention
of user names or passwords, so I thought it couldn't be done. Thanks for
that information, Tim.

Unfortunately, the help file does say that the IN clause can be used to
connect to only one external database at a time.

--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it impossible for
me to use a real e-mail address in public newsgroups. E-mail replies to
this post will be deleted without being read. Any e-mail claiming to be
from brenreyn at indigo dot ie that is not digitally signed by me with a
GlobalSign digital certificate is a forgery and should be deleted without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll find
a useable e-mail address at the URL above.