Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Another Dirt Simple One

Posted on 2000-03-23
18
Medium Priority
?
185 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 8
  • 8
  • 2
18 Comments
 
LVL 10

Expert Comment

by:caraf_g
ID: 2650194
The name of a table.
0
 
LVL 10

Expert Comment

by:caraf_g
ID: 2650206
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
ID: 2650300
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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
LVL 10

Expert Comment

by:caraf_g
ID: 2650322
0
 

Author Comment

by:diek_nf
ID: 2650468
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
ID: 2650500
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
ID: 2652473
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
ID: 2652488
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
ID: 2653101
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
 

Author Comment

by:diek_nf
ID: 2653123
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
ID: 2653553
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
ID: 2653750
caraf,
I'll check it out.
diek
0
 

Author Comment

by:diek_nf
ID: 2665259
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
ID: 2668166
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
ID: 2668984
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 90 total points
ID: 2669034
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
ID: 2712200
Thank you! Check out my current question on error handling please.
diek
0
 
LVL 10

Expert Comment

by:caraf_g
ID: 2712223
"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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

There are many ways to remove duplicate entries in an SQL or Access database. Most make you temporarily insert an ID field, make a temp table and copy data back and forth, and/or are slow. Here is an easy way in VB6 using ADO to remove duplicate row…
If you have ever used Microsoft Word then you know that it has a good spell checker and it may have occurred to you that the ability to check spelling might be a nice piece of functionality to add to certain applications of yours. Well the code that…
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…
Suggested Courses

618 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