asked on

Using Access - Please

Help! I need to set up an Access database that will hold question topics and a list of the filenames that are associated to that particular topic. The actions I need is to add/remove topics and add/remove question files associated with a topic. What This should let me do is add a topic to a list of topics in VB or remove a topic from the list; and to select a topic in the list of topics and add a filename to that topic category.
The point I am trying to get to is to be able to select a topic and have access to all the files relating to that topic. -Please let me know if I should be more clear.
This is an integral part of a program I am trying to complete so any help is greatly appreciated. Thanks, Daron

Hi,

I think you should create a database with two tables:
1. Topics
- ID; Autonumber; Primary key!
- Description; Text
2. Files
- Topic ID; Number; Long
- File path; Text
Then you should create a relation between these two tables. Relate the ID field from the Topics table to the Topic ID field in the Files table.

tkuppinen

Are you looking for the db structure ?

If so if think you would need a three table structure with topics, filenames, and a join table with topics and filenames. This way you can associate filenames with multiple topcis.

Continued...

Sorry I hit Submit accidentaly.

Anyway, when you have the tables set up, you have to create a form that shows records from the Topics table, and have a subform on it, which (surpisingly) displays records from the Files table. When you put the subform on the form, make sure wizards are enabled, taht will help you to easely specify the relations between the form and the subform (similar to the relations between the tables).

Of course, this is just a quick solution, but I think this would be a good starting point.

Hope I understood you right and this is what you needed.

aidan_gill

Daron

What I suggest is that you use 2 tables in access and then use the unique primary key you refer to the filename.

ie: TopicTable ->
ID TopicNum (UniquePriKey) TopicText

FileTable ->
ID TopicNum File Text

Then what I suggest that you do is when you do a search in Topic table you can link to the TopicNum in the FileTable.

Something like.

rst1 = "Select * from TopicTable"

Do While not rst1.eof
rst2 = "select * from FileTable where TopicNum = " & rst1.TopicNum

loop

They code above is very rough, I hope it gives you a good idea what to do , if not please ask and I will explain a bit better for you

regards

daron

ASKER

Yes. I am not familiar with any of the calls from VB to accomplish this, so any you can provide is helpful. I have looked at some fundamental definitions of the table, records...etc in Access but I can't seem to get the right code to get this to work.

OK, now, I have a some questions.

Reading the comment of Tkuppinen, he suggested something I did not thought of reading your question. Are you want to be able to assign any file to any topics and vice versa? If yes then you need to create a many-to-many relationship. To do this you'll need the third table just like he said.

So, you want to create the database and the tables and whole stuff from VB? I'd say it's easier to do the creation within Access, where you can use its menus and interactive designers to do it, which is much easier.

Do you want to display the data in a VB application or is it good if your Access database would contain one or two forms to do it?

Please let me know.

daron

ASKER

here's a form example of the VB interface I'm thinking of if it helps any.

VERSION 5.00
Begin VB.Form Form1
Caption = "Form1"
ClientHeight = 7140
ClientLeft = 60
ClientTop = 345
ClientWidth = 9030
LinkTopic = "Form1"
ScaleHeight = 7140
ScaleWidth = 9030
StartUpPosition = 3 'Windows Default
Begin VB.CommandButton Command3
Caption = "<--"
Height = 495
Left = 3000
TabIndex = 8
Top = 4680
Width = 735
End
Begin VB.CommandButton Command2
Caption = "-->"
Height = 495
Left = 3000
TabIndex = 7
Top = 4080
Width = 735
End
Begin VB.ListBox List2
Height = 2790
Left = 720
TabIndex = 5
Top = 3600
Width = 2175
End
Begin VB.FileListBox File2
Height = 2820
Left = 3720
TabIndex = 4
Top = 3600
Width = 2175
End
Begin VB.CommandButton Command1
Caption = "add new topic to list"
Height = 375
Left = 3480
TabIndex = 3
Top = 1080
Width = 2535
End
Begin VB.TextBox Text1
Height = 375
Left = 3480
TabIndex = 1
Text = "Text1"
Top = 600
Width = 2655
End
Begin VB.ListBox List1
Height = 2400
Left = 720
TabIndex = 0
Top = 360
Width = 2175
End
Begin VB.Label Label3
Caption = "topic list:"
Height = 255
Left = 720
TabIndex = 11
Top = 120
Width = 1335
End
Begin VB.Label Label2
Caption = "All files in root directory:"
Height = 255
Left = 3720
TabIndex = 10
Top = 3360
Width = 1935
End
Begin VB.Label wh
Caption = $"Form1.frx":0000
Height = 1695
Left = 6480
TabIndex = 9
Top = 1560
Width = 2055
End
Begin VB.Label s
Caption = "selected group files:"
Height = 255
Left = 720
TabIndex = 6
Top = 3360
Width = 2175
End
Begin VB.Label Label1
Caption = "new topic name"
Height = 375
Left = 3480
TabIndex = 2
Top = 240
Width = 1575
End
End
Attribute VB_Name = "Form1"
Attribute VB_GlobalNameSpace = False
Attribute VB_Creatable = False
Attribute VB_PredeclaredId = True
Attribute VB_Exposed = False

daron

ASKER

Adjusted points from 110 to 115

daron

ASKER

Ok. I have painfully created a DB with 2 tables - one called topicList that has 2 fields, topicName and ID. The second table is called topicFiles and also has 2 fields, fileName and ID. I've created a 1 to many relationship with topicList's ID field as the primary key and topicFiles ID field as the foreign key. So this makes pretty good sense, since I knew a little SQL previously. I have made this relationship in Access directly. My problem is how to create instances of this DB from VB6 and get the list info from the tables. I'm not sure what command to use to get a topic and the files associated with that topic. Also, I'm not sure what I am to do for the ID fields from VB. I hope I am making some sense of this, as it has taken some time to get to this point.

OK, I just want to make sure something:
The ID field in your second table should not be the ID of the files, there is no ID needed for those. That field should be the ID of the topic the file belongs to. Please double-check if the data types are as I suggested in my first comment. This is very important. I also suggest you to change the name of the ID field in the 2nd table (the topicFiles table) to TopicID because it would better refelect the real function of that field.

I suppose we can go step by step, that way we can verify the successful execution of each step at a time. And of course others will have a chance to comment.

First let's go keep the database open for as long as your form is open.

You need to make sure that the Microsoft DAO library is added to your project references.

To the General - Declarations section of your form, add the following code:
Dim DB as Database

To open the database with the form, add this to the Form - Load event:
Set DB=Workspaces(0).OpenDatabase("Put_the_database_path_here")

And of course it is neccessary to close it when the user closes the form, so to the Form - Unload event goes this:
DB.Close

OK, that's it for now. I'll be here at work for two more hours, then I'm off for 12 hours.

Let me know how it went and if you want to change something.

daron

ASKER

Right. The ID I changed to TopicID which is the foreign key having the value of ID in the topicList table. The primary key is ID in the table(topicList). I also checked the "enforce referential integrity" box (I think is supposed to be.) The relationship is one-to-many.

On a side note, I did this with Access2000 and the VisData in VB doesn't recgonize it. I'm not sure if it is a problem or I just need to add another control. What I have now is the regular "data" control and I just added the "ADO". I don't see the "DAO" to add.

In case it helps any here is a sketch of the form I'm working with:

The form I have is a MDI child called frmCreateQuiz, with a textbox called txtTopics for the topics, and another textbox called txtFiles for the "chosen" files for that topic. The last is a FileListBox called File1 that displays all the available file. Two command buttons, one "cmdAdd" and "cmdRemove" used for adding or removing a file from the topic. A textbox "txtNewTopicName" used to add a new topic by clicking the "cmdAddNewTopic" button.

I've included the lines for the form_load and unload, but stopped short of changing any of the control properties in case I need to use a different one.

Thanks so much for your patience.

No problem.

OK, first I think you meant listbox for the txtTopics and txtFiles. If not, you'll need to change those to be list boxes.

Here is one question: how many topics are we talking about? I just had an afterthought and I realized that the code I gave you is not a good starting point if ther will be a more then 100 records...

OK I never used Access 2000 before, so I don't know what differences there are in the file format.

Like I said I will be off line for 12 hours now.

I had an idea: if you need this quickly, or if you think it would be more comfortable for you, then just e-mail me your Access 2000 database (if you can without opening proprietary info) to me at peter.papp@halliburton.com and I'll see what can I do with it. If you let me know your e-mail address then I can send the complete solution to you tomorrow, with commented source code.

Bye for now

ASKER CERTIFIED SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Daron1

This is perfect. It works just like I want. I'm not sure why, it runs great in its own .vbp as you set up, but when I copy and add the form QuizForm.frm, include wQuizBas.bas, and copy the WQuiz97.mbd into the existing project - it runs but doesn't access the database(ie, no data in the combo or listbox.) I've stepped through the code but didn't see anything that looked unusual. I've also checked the references for the project and the controls, which match also. Is there something I may not be doing as it relates to the database specifically?

daron

ASKER

This could be a clue. When I first ran it in my own .vbp, this was the error messages it gave in the log file:

Line 92: Property Bindings in DBList1 had an invalid file reference.
Line 104: Property Bindings in DBCombo1 had an invalid file reference.This could be a clue. When I first ran it in my own .vbp, this was the error messages it gave in the log file:

Line 92: Property Bindings in DBList1 had an invalid file reference.
Line 104: Property Bindings in DBCombo1 had an invalid file reference.

When you copy the form, make sure you copy the .frx file, too. The bindings property usually refers to an offset in that file.

Hope this helps!

daron

ASKER

I checked the properties for the dbcombo and dblist, comparing them to the project that was working and found the rowsource was not copied when I copied the form. When I set this it works fine.
I've developed a performance problem somehow when I added the DB to my project, but I will have to check it on another computer to find if it is my computer causing it or something in my code. I posted this as a new question as you have been more than generous in your help and patience. You are credited with putting my boss in a good mood :). Thanks a million.