Solved

Another Dirt Simple One

Posted on 2000-03-23
18
179 Views
Last Modified: 2010-05-02
Hi,
I have a client/server program that is secured with the Workgroup Adm, I created a db and recordset in the login form.
-------------------------------------
DBEngine.SystemDB = _
            "C:\Windows\System\system.mdw"
           
s_UserName = txt_UserName.Text
s_PassWord = txt_PassWord.Text

Set workspace_BBI = DBEngine.CreateWorkspace("New", _
                                                            s_UserName, _
                                                            s_PassWord)
SecuredDB = _
                  "C:\Database\bbi.Pork"

Set db_BBI = DBEngine.Workspaces(0).OpenDatabase(SecuredDB)

Set rs = db_BBI.OpenRecordset("Select * FROM Admin")
-------------------------------------

This code works fine, but do I have to declare a new recordset and db on every form that accesses the database? I realize if I want to get to a different table, eg "CLient" I will have to declare a new rs, but should I be opening and closing the database and/or recordset on every form.

Lastly, a ms knowledge base article suggests using:
-------------------------------------
Set rs = db_BBI.OpenRecordset("Name", dbOpenDynaset)
-------------------------------------
They weren't very clear about what Name is supposed to mean. Any suggestions?
Thank you.

diek
0
Comment
Question by:diek_nf
  • 8
  • 8
  • 2
18 Comments
 
LVL 10

Expert Comment

by:caraf_g
Comment Utility
The name of a table.
0
 
LVL 10

Expert Comment

by:caraf_g
Comment Utility
You could declare a recordset as a global variable in a .bas module

Global gobjAdminRS As Recordset

Then you can set it somewhere at the start of your program
Set gobjAdminRS = db_BBI.OpenRecordset("Select * FROM Admin")

And don't close it.

I'd say this only makes sense for recordsets through which you can freely move backwards and forwards and on data that is fairly stable and unchangeable. E.g. a table containing US State abbreviations and descriptions.
0
 
LVL 5

Expert Comment

by:Voodooman
Comment Utility
Hi declare the Workspace and db as global in a module

Dim WS as workspace
Dim db as database

Set WS = DBEngine.Workspaces(0)
Set db = WS.OpenDatabase("C:\Exepos\Exepos32.mdb", False, False, "MS Access;UID=Voodoman; pwd=voodoo")

Any tables you open by any method will be attached to the workspace - no password or userid required. Adding a datacontrol to a form just adds the table to the workspace.

Good Luck

Voodooman!



0
 
LVL 10

Expert Comment

by:caraf_g
Comment Utility
0
 

Author Comment

by:diek_nf
Comment Utility
Caraf
What is the link about, my system at work has no sound card so I won't hear whatever it is.
diek
0
 

Author Comment

by:diek_nf
Comment Utility
voodoo,
I can't understand why you insist on locking my questions. I am happy you took the time but I would like to hear from as many people as possible. When you answer you lock the question and prevent others from participating.

Anyways, explain the false false. Should I open and close the db and or rs on every form?
0
 
LVL 10

Expert Comment

by:caraf_g
Comment Utility
Hi diek, it's a bit of Irish Humour. Do you know the TV Series "Father Ted"?

If you do you'll appreciate this. It's Father Jack shouting "Gobshite" in his usual diplomatic manner.

If you don't, never mind. ;-)

PS - "Gobshite", although it may sound offensive, is only a mild expletive over here so Voodoo, I hope you're not offended.
0
 
LVL 5

Expert Comment

by:Voodooman
Comment Utility
I locked the question because its the right answer.

If you think its dirt simple why cant you do it yourself?

If you want to know what false,false means read the documentation.

I notice from looking at your previous questions that you dont give points - this is not playing the game....

People answer questions to get points...

I am a very busy programmer I answer for points and to help NICE people..

Voodooman!
0
 

Author Comment

by:diek_nf
Comment Utility
Voodoo,
First, I normally give as many points as I can, currently I have 20 points. When I posted this question, I had 35 -40. So if I had more points I would have gladly given them up. Initially I when I joined I gave up 100 points at a shot but then I quickly had no points. And I could not ask any questions.

When you wrote "People answer questions to get points... ", that is a pretty sweeping assumption. The last question you helped me with was answered by g(I think), and I gave him the points. Then when I had a follow up question both glen and g helped me for no points. They just helped. I had one guy offer to convert a lotus notes db for me this morning. So people just do not participate just for points.

I am sorry if I sounded snotty in my reply to your answer. I didn't mean to.

I do not understand why wanting to hear from as many people as possible, pisses you off.

Lastly, I try to give my questions interesting subject titles. I find far more people participate. When I used "dirt simple" I do not mean for me. I just took over a program and I haven't worked in vb in a while, I am new to vb 6, and my mind is still in java mode, so getting into the swing of things when my boss is having a mental breakdown is causing some pressure.

You are correct I should have checked false false in the docs. I hate the html help that comes with visual studio.

diek
0
What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

 

Author Comment

by:diek_nf
Comment Utility
caraf,
I am from newfoundland and my ancestors are pure Irish, so I understand Irish humour perfectly. I am not up on Father Ted. I am more of a "seinfield","frazier""sex in the city", "beggars and choosers" tv watcher.
diek
0
 
LVL 10

Expert Comment

by:caraf_g
Comment Utility
Search for "Father Ted", "Father Dougal", "Father Jack" and "Mrs Doyle" on the Web to get an idea what it is all about. Basically it is a TV Series based on the idea of three mad priests and their housekeeper who live on this small island off the Irish coast...

Alas, the main actor, Dermot Morgan, who played Father Ted died recently of a heart attack.

Here's the site I got the sound bite from:
http://www.geocities.com/TelevisionCity/Studio/1728/pages/main.html
0
 

Author Comment

by:diek_nf
Comment Utility
caraf,
I'll check it out.
diek
0
 

Author Comment

by:diek_nf
Comment Utility
Hello,
If have annoyed or offended those trying to help me please let me know. I am still interested in solving this problem, please help.

diek
0
 
LVL 10

Expert Comment

by:caraf_g
Comment Utility
not at all diek, actually, it's me who started all this "Father Ted" nonsense so people probably forgot about your original question. Apologies!

Didn't the suggestions work?
0
 

Author Comment

by:diek_nf
Comment Utility
Caraf and other Helpful types,
First,if you think this is too much work for 30 points, I will understand.

I declared the record set in global variable in a .bas module like you said but it is still acting funny(unfortunately not ha ha funny). Anyways, here is the part that is currently aggravating me, I have searched through most of my books and docs and I cannot seem to find the following:

Once a database(I am using one) is declared and opened, in a multi-form, multi-table program...do I have to open the recordset each time I want to use it(the same recordset), then immediately close it once I no longer need it?

Do I have to declare the record set each time I need it?

I declared it like you said in a .bas, but it doesn't seem to recoginize it in another form.

=====================================
s_UserName s_PassWord are declared globally

=======================================
FIRST DECLARATION - Form A
========================================
Private Sub cmd_OK_Click()
Mode = 0

DBEngine.SystemDB = "C:\Windows\System\system.mdw"

s_UserName = txt_UserName.Text
s_PassWord = txt_PassWord.Text

Set workspace_BBI = DBEngine.CreateWorkspace("New", _
                                                            s_UserName, _
                                                            s_PassWord, dbUseJet)
SecuredDB = "C:\Database\bbi.mdb"

Set bbi_database = DBEngine.Workspaces(0).OpenDatabase(SecuredDB)

Set admin_rs = bbi_database.OpenRecordset("Admin", dbOpenDynaset)
========================================
SECOND TIME FAILS TO WORK - Form B
=======================================
Private Sub Form_Load()

Set bbi_database = Nothing
Set admin_rs = Nothing
Set workspace_BBI = Nothing

s_UserName = ""
s_PassWord = ""
   
'Get Password Authorization
s_UserName = InputBox("Enter Your Username", s_UserName)

s_PassWord = InputBox("Enter your Password", s_PassWord)

DBEngine.SystemDB = "C:\Windows\System\system.mdw"
   
Set workspace_BBI = DBEngine.CreateWorkspace("New", _
                                        s_UserName, _
                                        s_PassWord, dbUseJet)
SecuredDB = _
            "C:\Database\bbi.mdb"

Set bbi_database = DBEngine.Workspaces(0).OpenDatabase(SecuredDB)

Set admin_rs = bbi_database.OpenRecordset("Admin", dbOpenDynaset)

'Fails Here

admin_rs.MoveFirst
---------------------------------------
Thank you
diek

0
 
LVL 10

Accepted Solution

by:
caraf_g earned 30 total points
Comment Utility
Perhaps the recordset is empty? If Admin_rs.EOF and Admin_rs.BOF then
    'Recordset is empty...

Another thing to try (wild guess) is use dbOpenDynamic?
0
 

Author Comment

by:diek_nf
Comment Utility
Thank you! Check out my current question on error handling please.
diek
0
 
LVL 10

Expert Comment

by:caraf_g
Comment Utility
"Check out my current question on error handling please"
Nope.

If you'd awarded me with an "A", yes. But giving me a "B" for correctly *comment*ing on (not *locking*) a 30-point question amounts to an insult so, forget it, mate.
0

Featured Post

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

Have you ever wanted to restrict the users input in a textbox to numbers, and while doing that make sure that they can't 'cheat' by pasting in non-numeric text? Of course you can do that with code you write yourself but it's tedious and error-prone …
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…

771 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

14 Experts available now in Live!

Get 1:1 Help Now