Solved

Using Access - Please

Posted on 2000-05-11
17
237 Views
Last Modified: 2010-05-02
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
0
Comment
Question by:daron
17 Comments
 
LVL 1

Expert Comment

by:4P
ID: 2800845
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.
0
 
LVL 1

Expert Comment

by:tkuppinen
ID: 2800863
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.
0
 
LVL 1

Expert Comment

by:4P
ID: 2800887
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.
0
 
LVL 2

Expert Comment

by:aidan_gill
ID: 2800888
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
0
 

Author Comment

by:daron
ID: 2800889
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.
0
 
LVL 1

Expert Comment

by:4P
ID: 2800978
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.
0
 

Author Comment

by:daron
ID: 2800985
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
0
 

Author Comment

by:daron
ID: 2804811
Adjusted points from 110 to 115
0
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 

Author Comment

by:daron
ID: 2804812
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.
0
 
LVL 1

Expert Comment

by:4P
ID: 2804920
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.
0
 

Author Comment

by:daron
ID: 2805390
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.

0
 
LVL 1

Expert Comment

by:4P
ID: 2805447
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
0
 
LVL 1

Accepted Solution

by:
4P earned 115 total points
ID: 2806632
I sent the complete program back to you.

I could not use your Access 2000 database, so I created another one with Access97.

I also changed one thing: instead of the file list box, I used the commondialog, I think it gives more freedom to both the programmer and the user.

And, finally, I included an "Open file" button for so the selected files can be opened easiely.

Some things I did not really worry about, like if the user tries to add a file while there is not yet any topics selected, then it will probably die. I thought the big thing was the database part of the stuff...

And just one more comment: the "regular" data control is the DAO control.

Anything else please let me know.

Bye
Péter
0
 

Expert Comment

by:Daron1
ID: 2807099
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?
0
 

Author Comment

by:daron
ID: 2807163
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.
0
 
LVL 1

Expert Comment

by:4P
ID: 2809454
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!
0
 

Author Comment

by:daron
ID: 2810405
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.  
0

Featured Post

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Background What I'm presenting in this article is the result of 2 conditions in my work area: We have a SQL Server production environment but no development or test environment; andWe have an MS Access front end using tables in SQL Server but we a…
Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
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 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…

708 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

17 Experts available now in Live!

Get 1:1 Help Now