Create secýred database via code ADOX?

[Özden Irmak]

Hi,

Does anybody have any sample code to create a database via ADOX catalog
object and join it to a workgroup file?

Thanks,

Özden

 

[Brendan Reynolds]

I looked into this but didn't get very far. ADOX help isn't functioning on
my system, so I'm afraid I don't think there's much more I can do, but this
is as far as I got, if you want to investigate it futher yourself ...

Public Sub CreateDatabaseADOX()

'Connection string below copied from a UDL file.
'Provider=Microsoft.Jet.OLEDB.4.0;
'Data Source=C:\Documents and Settings\Brendan Reynolds\My
Documents\db1.mdb;
'Persist Security Info=False;
'Jet OLEDB:System database=C:\Documents and Settings\Brendan Reynolds\My
Documents\Custom.mdw

Dim cat As ADOX.Catalog
Dim strConnect As String

Set cat = New ADOX.Catalog
cat.ActiveConnection = CurrentProject.Connection
strConnect = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\Documents and Settings\Brendan Reynolds\My
Documents\ADOXTest.mdb;" & _
"Persist Security Info=False;" & _
"Jet OLEDB:System database=C:\Documents and Settings\Brendan
Reynolds\My Documents\Custom.mdw"
cat.Create strConnect

End Sub

Code fails with following error message ...

-2147217887 (80040E21)
Multiple-step OLE DB operation generated errors. Check each OLE DB status
value, if available. No work was done.

--
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.

 

[Özden Irmak]

Dear Brendan,

Thank you for your help...Let me describe you what I want to achieve...

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 ADO. (Nor you can do from inside Access
manually)

2) Tried to import the tables onto a common database. 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.

What on earth does access 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 got stuck in a stupid problem like
this...

Please let me know if any solution comes into your mind and thanks again for
your attention on my issue...

Özden

 

[Brendan Reynolds]

Are these two databases secured with the same workgroup file, or is each
database secured with a different workgroup file?

--
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.

 

[Özden Irmak]

Well, in this sample it's same but it may be possible as different. I would
like to hear if you have any solution when they are same?

Regards,

Özden

 

[Brendan Reynolds]

The third database with links to the tables in the other two looks like the
way to go as long as all databases are using the same workgroup file. Do I
understand you correctly that for some reason creating this third database
manually during the design stages is not an option, for some reason you have
to do this dynamically at run-time?

BTW: None of this seems to have much to do with replication or table design,
I suggest we remove those newsgroups from the list and keep this to
microsoft.public.access and microsoft.public.access.security.

--
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.

 

[Özden Irmak]

Dear Brendan,

Let me be more clear with you...This is a tool designed to compare and
synchronize data differences in Access databases...

The easiest and most performance gainy way to compare data is to link the
tables which are going to be compared into a single database and doing a SQL
query to do the comparison. At this point, the tool creates and empty
database and links the other two database tables into this newly created
database so it can do the comparison.

This works flawlesly when the two compared databases don't have any security
or have only password protection...

After all this explanation I can say that your understanding is completely
right...

Regards,

Özden

P.S.: Removed the non-related newsgroups, sorry.

 

[Brendan Reynolds]

It might be possible to do something using DAO as long as the two databases
were secured using the same workgroup file, but I suspect that any solution
along those lines would probably break the first time you had to deal with
databases secured using different workgroup files. If you still want to
pursue that idea, I think you'll probably need to use the undocumented
PrivDBEngine. This is not something that I've used myself, just something
that I have heard about. I can't tell you how to use it, all I can do is
point you toward a couple of resources ...

http://www.google.ie/groups?as_q=pr...ases.ms-access&as_scoring=d&lr=&num=100&hl=en

http://www.mvps.org/access/modules/mdl0025.htm

Unless someone else has a better idea, perhaps it may be necessary to not
use queries but to loop through recordsets instead. I do understand your
desire to use the most efficient method, but in order to be considered
efficient a method must also actually work - if executing queries won't work
then perhaps looping through a recordset is the most efficient method in
these circumstances.

If the comparison is complex and might involve significant 'jumping around'
within the recordsets, it might pay to open the recordsets and then write
from the recordsets to local tables. You can then base your queries on those
local tables.

--
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.

 

[Joan Wild]

It might be possible to do something using DAO as long as the two
databases were secured using the same workgroup file, but I suspect
that any solution along those lines would probably break the first
time you had to deal with databases secured using different workgroup
files. If you still want to pursue that idea, I think you'll probably
need to use the undocumented PrivDBEngine. This is not something that
I've used myself, just something that I have heard about. I can't
tell you how to use it, all I can do is point you toward a couple of
resources ...

It's explained in the security FAQ - section 46 "How can I open a database
in code that was secured using another workgroup file?"


ADOX is not the way to go - see section 45 of the FAQ
http://support.microsoft.com/?id=207793

 

[Brendan Reynolds]

Thanks, Joan. I see I'm going to have to stop calling PrivDbEngine
'undocumented'. It's not undocumented, it's just 'unsupported'!

--
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.

 

[Joan Wild]

Thanks, Joan. I see I'm going to have to stop calling PrivDbEngine
'undocumented'. It's not undocumented, it's just 'unsupported'!

I wasn't targeting your use of the word undocumented. Just wanted to direct
Özden to other/more information.

 

[Brendan Reynolds]

I understood that, Joan, but I appreciate you saying it anyway.
Misunderstandings can happen so easily when we have only text to judge by -
'emoticons' are a poor substitute for body language and tone of voice!

--
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.

 

[Joan Wild]

I understood that, Joan, but I appreciate you saying it anyway.
Misunderstandings can happen so easily when we have only text to
judge by - 'emoticons' are a poor substitute for body language and
tone of voice!

Ain't that the truth! I think I often come across differently than I mean,
so am careful to watch for any sign of misunderstanding.

There are times I hate the writing as a form of communication.

 

[Tony Toews]

It's explained in the security FAQ - section 46 "How can I open a database
in code that was secured using another workgroup file?"
ADOX is not the way to go - see section 45 of the FAQ
http://support.microsoft.com/?id=207793

Sure glad somebody has read that FAQ. I certainly haven't.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm

 

[Lynn Trapp]

Sure glad somebody has read that FAQ. I certainly haven't.

Well, it's about time you did... <g>

 

[Tony Toews]

Well, it's about time you did... <g>

<shudder>

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm