Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    New Lounger
    Join Date
    Oct 2002
    Location
    Leicester, Leicestershire, England
    Posts
    9
    Thanks
    0
    Thanked 0 Times in 0 Posts

    VB6 (Visual Basic)

    I have a database (written in VB6) of names and addresses. I need to produce a second database to contain details of goods purchased by people in the first DB. I understand that this would be a relational DB but I have no idea how to set these up so that given a name (or ID) in the first DB the second DB will show the goods purchased only by the given name.
    Can anybody help? Thanks.

  2. #2
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: VB6 (Visual Basic)

    When you say "written in VB", do you mean you used VB to create a Jet mdb, or are you talking about simply creating a text file with the information in it? If it's an mdb, you don't need another database, you merely need another table in the same database.
    Charlotte

  3. #3
    New Lounger
    Join Date
    Oct 2002
    Location
    Leicester, Leicestershire, England
    Posts
    9
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VB6 (Visual Basic)

    Yes, I mean a DB written in VB6. The problem is still the same; how do I get the first table to display information in the second? My instinct tells me that the table would need to be relational but how to do that?

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: VB6 (Visual Basic)

    You say "a DB written in VB6" - but VB6 is a programming language, not a database system. You can create, modify, ... a database in a VB6 program, but the "database" will be a table in an Access database, or a table in a SQL Server database, or a text file. How do you connect to the database in your VB6 program? If you can give some details of the connection, and/or of the file name (including extension) of the database file you have created, we may be able to help you. Now, we simply don't have enough information.

  5. #5
    New Lounger
    Join Date
    Oct 2002
    Location
    Leicester, Leicestershire, England
    Posts
    9
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VB6 (Visual Basic)

    My files are created in VB6 through the

  6. #6
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: VB6 (Visual Basic)

    We still aren't on the same page. Access/Jet databases don't have fields, they have tables and the tables have fields. If you have a jet database that contains name and address information in a table, you can create another table in the same database and put the purchases in that. Both tables will be in the same database file. You would include the ID field from the customer table as a foreign key in the purchases table and you would create a one-to-many relationship between the two tables.

    Did you use the Visual Data Manager add-in to create the database? You did create an Access database, right, not text or something else? If you did, then just open the Visual Data Manger again and create and execute a new CREATE TABLE query to add a new table to the database. Include a field that uses the FOREIGN KEY constraint and the REFERENCES keyword to create the relationship between the fields in the two different tables.
    Charlotte

  7. #7
    New Lounger
    Join Date
    Oct 2002
    Location
    Leicester, Leicestershire, England
    Posts
    9
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VB6 (Visual Basic)

    Hi Charlotte,

    Sorry I

  8. #8
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: VB6 (Visual Basic)

    There's no need to apologize. The problem is that it isn't clear what your asking. If you want to relate the two tables, then one of them has contain a foreign key field that relates to a field in the other table. If you're trying to relate them on a name (not a good name for a field, by the way), then you would have to have the name field in both tables so that you could relate the records properly. Is that any help?
    Charlotte

  9. #9
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts

    Re: VB6 (Visual Basic)

    You might want to pick up a book on database programming or read the online documentation at microsoft.com on database design.

    In brief, you design tables to mirror real-world objects. The first table might be called "customers," the second might be called "goods" and the third might be called "purchases." Of course, you can call them whatever you want; these are just conceptual names. You then can create an efficient record of a purchase as follows:

    Purchase ID
    Customer ID
    Goods ID
    Price (if this is something that could change, and therefore can't be reliable retrieved from the Goods table)
    Date

    To minimize all duplication, you could split this into two tables, but I think it's probably beyond your needs.

    To retrieve a person's purchases, you would build a query that joins all three tables and returns the relevant columns (fields) from each one as a set of data, probably using ADO. If you need to get more into the database design and queries, you might visit the Access board, where the database experts hang out.

  10. #10
    New Lounger
    Join Date
    Oct 2002
    Location
    Leicester, Leicestershire, England
    Posts
    9
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VB6 (Visual Basic)

    I'm about ready to chuck the computer out of the window!

    All I want to do is write a program to access a database. The DB has two tables; the first table has two fields, custid and customer. The second table also has two fields, custid and cost. The two table should be linked to the first so that reading the customer name will allow me to see the cost assosiated with that customer. Now, HOW do I write this program? Having got this idea working it maybe that I will add other fields to each table - but lets get the first idea working first.

  11. #11
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: VB6 (Visual Basic)

    Tony, as you are learning, it is really tough to pick-up database programming on your own. You definitely want to pick up a VB book that covers DB programming.

    Attached is a zipped VB project and database that is as simple as I can make it. I created a database with VB's Visual Data Manager. It contains two tables, tblCustomer and tblPurchases. tblCustomer has two fields, fldCustomerID & fldName. tblPurchases has three fields, fldCustomerID, fldDate, & fldPrice. After creating the tables, I also used the Visual Data Manager to add some rows to the tables. I then created a form with 3 textboxes and an ADO control. I setup the ADO connection string using the build button and set the ADO RecordSource to be "select * from tblCustomer,tblPurchases where tblCustomer.fldCustomerID=tblPurchases.fldCustomer ID." This is the answer to your question: it is the simplest way to join the two tables together, however it does not let you update the tables. Finally, I set the DataSource of the textboxes to be the ADO control and set the DataField to be fldName, fldDate, & fldPrice, respectively. HTH --Sam
    Attached Files Attached Files
    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

  12. #12
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: VB6 (Visual Basic)

    To add to Sam's suggestion, take a look at Beginning Visual Basic 6 Database Programming, by John Connell (ISBN 1-861001-06-1) and if you aspire to professional developer status and client-server development, check out Database Access with Visual Basic 6, by Jeffrey P. McManus (ISBN 0-672-31422-3).
    Charlotte

  13. #13
    New Lounger
    Join Date
    Oct 2002
    Location
    Leicester, Leicestershire, England
    Posts
    9
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VB6 (Visual Basic)

    Sam,
    Gee thanks, I got the program up and running (with a small change to the DB address). This is the first time I have seen this approach to programming, it certainly would stand looking at in detail. All my programming has been done by writing code to read, add, and delete items from the DB. Yes I have some books on VB6 (6 of 'em) but none of them covers relational DB's. I tried to follow the example in the DB help files (the one using the Northwind DB) but its so involved that it lost me. I see Charlotte has noted a book DB's, I shall try and get it.
    Anyway, very many thanks for your help, maybe in time I shall be able do as your text asks!

  14. #14
    New Lounger
    Join Date
    Oct 2002
    Location
    Leicester, Leicestershire, England
    Posts
    9
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VB6 (Visual Basic)

    Thanks Charlotte, see also my reply to Sam. I shall see NOW if I can find the book on Databases Mcmanus!

  15. #15
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: VB6 (Visual Basic)

    You might want to try the Connel book first, since it's a beginning level intro to developing databases using VB6. The McManus book is labelled as Professional user level.
    Charlotte

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •