Solved

Using MS Access 2000 database (mdb) in Visual Basic 6

Posted on 2004-09-28
13
60,990 Views
Last Modified: 2013-12-25
Hi!

I have a MS Access 2000 database containing a table.
The table has 10 fields which are of textual or numerical format.

What is the best/simplest/most reliable    way for me to connect to this database in Visual Basic 6?

These are some functions I'd like to be able to perform on those databases:
 - get the data under some Row/Col coordinates in a table
 - change the data under some Row/Col coordinates
 - add new columns (Records)
 - delete existing columns
 - search the table for some data

I'm not very familliar with Access and database programming, and I'd like to keep it as simple as possible.
I'm using the database only becouse I'm planning to have a large amount of data which is too difficult to manage using text files or similar "home-made" methods.

My first attempt to do it was to use Adodc and DataGrid controls to display a table, but I'm wondering if this is the good approach.
It gave me the ability to view and modify the exitsing data, but I was unable to find a way to add new Record (Column) or delete the existing one.

Also I didn't find a way to search through the table.
(There is a way to loop through all cells, and compar the data in each cell with the word I'm looking for, but I'm affraid it will become too slow as the database grows.)

Do you have any suggestions on how to connect to database and be able to perform these simple operations?

Thanks.
0
Comment
Question by:dbrckovi
  • 5
  • 4
  • 2
  • +2
13 Comments
 
LVL 3

Accepted Solution

by:
Arundel_Castle earned 150 total points
ID: 12169798
Hello,
       first off you shoul right click on the data grid, there you will see a list of properties for the data grid. Make sure the "AllowAddNew" Check box is clicked. This will add a blank row to the end of your data grid and allow you enter a new record. If you want to you can also set the delete option. Make sure that your ADO recordset properties are set correctly as well.

EG

Set RecEmployees.ActiveConnection = MainCon
RecEmployees.CursorLocation = adUseServer
RecEmployees.CursorType = adOpenDynamic
RecEmployees.LockType = adLockOptimistic
RecEmployees.Open "Employee", , , , adtable

Basically you don't want to create a non updatetable recordset if you want to update from the grid.

The above recodset should allow you to do all that you want. If you want to delete you will have to set up a event handler for the datagrid and delete the record with code. If you want to find a record you could use the ADO  Recordset.find method. This will find the record in the recordset and set your record pointer to that location.

There are a lot of things to learn with VB Programming and working with databases bases but the datagrid is a good place to start and probably the fastest for you at the moment. You might need to do some reading on the grid itself and how it operates. Also some reading on ADO is a must.

0
 
LVL 5

Assisted Solution

by:jkwasson
jkwasson earned 100 total points
ID: 12176837
I would recommend that you use ADO, but you should know that DAO is also available (although a bit harder to understand in my opinion). DAO is optimized to work with the Jet (Access) DB Engine.

Here is a link to some information to get you up and running with ADO. I recommend concentrating on learning these objects to begin with...

Connection
Recordset
Command

As far as the control you want to use, it depends on how you want to edit the data. the Microsoft Flex Grid control is one of my personal favorites, although editing records in place with it can be a chore. I will typically create a form with the flexgrid to allow the user to pick the record they want to edit, then I populate other controls on the form (textboxes, etc.) with the data from that record so they can edit the individual record.

Hope this helps!

0
 
LVL 11

Author Comment

by:dbrckovi
ID: 12177089
Thanks!

I'll try it in a few moments, but in the meanwhile can I ask another quick question?

How can I reset autonumber field in a table? My auto numbers are allways starting from different number (incremental).
I heard somewhere that this is a feature which prevents conflicts, but there is no data which could be conflicted yet.

So can I reset autonumbers to start back from 1?

( I'm not planning to use this field for anything. It's just annoying! )
0
 
LVL 5

Assisted Solution

by:jkwasson
jkwasson earned 100 total points
ID: 12177121
I don't think you can change autonumer field value for a table once you have added records to that table, but you can change the start value of the increment before you begin adding records. Here's how...(not worth the trouble if you ask me, but here is your answer anyway - took it from Microsoft access help :) )

If your original table contains property settings that prevent Null (Null: A value you can enter in a field or use in expressions or queries to indicate missing or unknown data. In Visual Basic, the Null keyword indicates a Null value. Some fields, such as primary key fields, can't contain Null.) values in fields, you must temporarily change those properties. These settings include:
The Required field property set to Yes
The Indexed field property set to Yes (No Duplicates)
A field and/or record ValidationRule property that prevents Null values in fields
Create a temporary table with just one field: a Number field. Set its FieldSize property to Long Integer and give it the same name as the AutoNumber field in the table whose value you want to change.
In Datasheet view (Datasheet view: A window that displays data from a table, form, query, view, or stored procedure in a row-and-column format. In Datasheet view, you can edit fields, add and delete data, and search for data.), enter a value in the Number field of the temporary table that is one (1) less than the starting value you want for the AutoNumber field. For example, if you want the AutoNumber field to start at 100, enter 99 in the Number field.
Create and run an append query (append query: An action query that adds the records in a query's result set to the end of an existing table.) to append the temporary table to the table whose AutoNumber value you want to change.
How?

Create a query that contains the table whose records you want to append to another table.
How?

In the Database window (Database window: The window that appears when you open an Access database or an Access project. It displays shortcuts for creating new database objects and opening existing objects.), click Queries  under Objects, and then click New on the Database window toolbar.
In the New Query dialog box, click Design View, and then click OK.
In the Show Table dialog box, click the tab that lists the tables or queries whose data you want to work with.
Double-click the name of each object you want to add to the query, and then click Close.
Add fields to the Field row in the design grid (design grid: The grid that you use to design a query or filter in query Design view or in the Advanced Filter/Sort window. For queries, this grid was formerly known as the QBE grid.), and if you want, specify criteria (criteria: Conditions you specify to limit which records are included in the result set of a query or filter.) and a sort order.
To view the query's results, click View  on the toolbar.
In query Design view (Design view: A window that shows the design of these database objects: tables, queries, forms, reports, macros, and data access pages. In Design view, you can create new database objects and modify the design of existing ones.), click the arrow next to Query Type  on the toolbar, and then click Append. The Append dialog box appears.
In the Table Name box, enter the name of the table you want to append records to.
Do one of the following:
If the table is in the currently open database, click Current Database.

If the table is not in the currently open database, click Another Database and type the path of the database where the table is stored or click Browse to locate the database. You can also specify a path to a Microsoft FoxPro, Paradox, or dBASE database, or a connection string to an SQL database (SQL database: A database that is based on Structured Query Language (SQL).).

Click OK.
Drag from the field list (field list: A window that lists all the fields in the underlying record source or database object, except in data access page Design view. In data access page Design view, it lists all the record sources and their fields in the underlying database.) to the query design grid (design grid: The grid that you use to design a query or filter in query Design view or in the Advanced Filter/Sort window. For queries, this grid was formerly known as the QBE grid.) the fields you want to append and any fields you want to use for setting criteria.
If all the fields in both tables have the same names, you can just drag the asterisk (*) to the query design grid. However, if you're working in a database replica (replica: A copy of a database that is a member of a replica set and can be synchronized with other replicas in the set. Changes to the data in a replicated table in one replica are sent and applied to the other replicas.), you'll need to add all the fields instead.

If you have a field with an AutoNumber data type (data type: The characteristic of a field that determines what type of data it can hold. Data types include Boolean, Integer, Long, Currency, Single, Double, Date, String, and Variant (default).), do one of the following:
Add AutoNumber values automatically

To have Microsoft Access add AutoNumber values automatically, don't drag the AutoNumber field to the query design grid when you create the query.

With this method, Access appends records and automatically inserts AutoNumber values. The first record appended has a value that is one larger than the largest entry that was ever entered in the AutoNumber field (even if the record that contained the largest AutoNumber value has been deleted).

Use this method if the AutoNumber field in the table you're appending to is a primary key (primary key: One or more fields (columns) whose values uniquely identify each record in a table. A primary key cannot allow Null values and must always have a unique index. A primary key is used to relate a table to foreign keys in other tables.), and the original table and the table you're appending to contain duplicate AutoNumber values.

Keep the AutoNumber values from the original table

To keep the AutoNumber values from the original table, drag its AutoNumber field to the query design grid when you create the query.

If the fields you've selected have the same name in both tables, Microsoft Access automatically fills the matching name in the Append To row. If the fields in the two tables don't have the same name, in the Append To row, enter the names of the fields in the table you're appending to.
In the Criteria cell for the fields that you have dragged to the grid, type the criteria on which additions will be made.
To preview the records that the query will append, click View  on the toolbar. To return to query Design view, click View  on the toolbar again. Make any changes you want in Design view.
Click Run  on the toolbar to add the records.
Delete the temporary table.
Delete the record added by the append query.
If you had to disable property settings in step 1, return them to their original settings.
0
 
LVL 11

Author Comment

by:dbrckovi
ID: 12177285
Thanks.

I have been able to make these basic functions work. It will be enough to start my project.
I know there's a whole world of databases for me to learn, but I'll learn it as I develop my app.

I'm experimental type. I can't help it. I need to have something "visible" before I learn theory.
This is why I never learned databases. All books were trying to teach me complete theory before showing me how this works in practice.

jkwasson

You're right. It's not worth the trouble :-)
This fileld won't be visible to the end user anyway.
0
 
LVL 2

Expert Comment

by:bdbrown
ID: 12216522
About the Auto Number Type Fields in Access Database tables;
Select "Tools > Database Utilities > Compact Database".
If your table has no records, this will reset the value back to 1. If your table has records, this will set the auto number sequence back to the next incremental number already in the field.

bdb
0
Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

 
LVL 11

Author Comment

by:dbrckovi
ID: 12225439
Thanks bdbrown.

Hi again to everyone!

Datagrid sollution still works. :-)
But now I'm trying to examine different approaches.

Currently I'm trying to learn how to use DAO objects to open some database.

I managed to open the databse and a table.
I can get the list of Fields and number of Records.

But I can't figure how to get a specific value from that table.

 - this is my code:
'--------------------------------------------------
Private Sub Command1_Click()
    Dim myDatabase As Database
    Dim myTable As TableDef
    Dim myField As Field

    Set myDatabase = OpenDatabase("e:\davor\videoteka\videoteka2.mdb")
    Set myTable = myDatabase.TableDefs("Filmovi")
    Set myField = myTable.Fields("Broj")
   
    For Each x In myTable.Fields
        Print x.Name
    Next x

    Print myField.Name
    Print myTable.RecordCount
End Sub
'-----------------------------------------------------

If the table looks like this:

======================================================
Number (auto)        Name           Surname              Address                Telephone
-------------------------------------------------------------------------------------------------
    1                    SDFSDa         dFGErgerg           ERTFFFR 45            11324454
    2                    FGHJRRJ        regdsffgdf            dfgsdfer  43            57456455
    3                    JGFBBDE        xcwEWDF            ERGDFErr  4             5645322
    4                    dfgETHh         trteRTEFF           retewrgG 5              12333222
    5                    SDFSDa         ASDASDA            SASDQ 2                 23423626
    6                    rtzGHRT         GHRTTTR            wESDFG2 3             66544433
=======================================================

How do I get a specific value (Cell)?            For example: How do I get and Address value of record under Number 5    (SASDQ 2)  ?

I'm using DAO 3.6.



If someone answers this, I'll open new "Points for ..." question, and point to it.
0
 
LVL 2

Expert Comment

by:bdbrown
ID: 12225941
dbrckovi;

You can use a lookup function on an indexed field using the Table type recordset as follows;
(assuming the field "Number" in your example is the "PrimaryKey" index)

'--------------------------------------------------
Private Sub Command1_Click()
    Dim myDatabase As Database
    Dim myTable As Recordset

    Set myDatabase = OpenDatabase("e:\davor\videoteka\videoteka2.mdb")
    Set myTable = myDatabase.TableDefs("Filmovi", dbOpenTable) ' open as Table-type

    myTable.Index = "PrimaryKey"
    myTable.Seek "=", 5 ' any number you wish to find in the indexed field

    If myTable.NoMatch then
        Print "Record not found"
    Else
         'Record found
         Print myTable.Fields("Address")
    End If

     Set MyTable = Nothing
     MyDatabase.Close

End Sub
'----------------------------------------------------    

You can also use the "FindFirst", "FindNext", "FindLast" methods of a dynaset- or snapshot-type recordset.

    Set myTable = myDatabase.TableDefs("Filmovi", dbOpenDynaset) ' open as Dynaset

    MyTable.FindNext "[Number] = 5"

bdb
0
 
LVL 11

Author Comment

by:dbrckovi
ID: 12234824
Hi!

I get an error in this line:          Set myTable = myDatabase.TableDefs("Filmovi", dbOpenTable)
It says: Wrong number of arguments or invalid property asignment.

As I can see, TableDefs recieves only one argument, but if I remove dbOpenTable, I get Type mismatch error in the same line.

What is wrong?
0
 
LVL 2

Expert Comment

by:bdbrown
ID: 12236427
Sorry,  my mistake - the line should read;

    Set myTable = myDatabase.OpenRecordset("Filmovi", dbOpenTable)  ' open as Table-type

bdb
0
 
LVL 11

Author Comment

by:dbrckovi
ID: 12246048
bdbrown:
Thanks. It works.

I have oppened the "Points for" question here:
http://www.experts-exchange.com/Programming/Programming_Languages/Visual_Basic/VB_Databases/Q_21159100.html

Post something there and you'll get the points you deserved.
0
 
LVL 1

Expert Comment

by:weeb0
ID: 12407281
Would like to ask a question beyond this one.

If access 2000 is not installed on the computer, is it possible to connect to the database ?????

if yes, with which connection type can we use ?  I know that adodb is not working ...

thanx
0
 
LVL 2

Expert Comment

by:bdbrown
ID: 12409605
weeb0;

You should really open a new post for a new question, especially if your not the author of the original question.

bdb
0

Featured Post

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

Introduction I needed to skip over some file processing within a For...Next loop in some old production code and wished that VB (classic) had a statement that would drop down to the end of the current iteration, bypassing the statements that were c…
When designing a form there are several BorderStyles to choose from, all of which can be classified as either 'Fixed' or 'Sizable' and I'd guess that 'Fixed Single' or one of the other fixed types is the most popular choice. I assume it's the most p…
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…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

762 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

20 Experts available now in Live!

Get 1:1 Help Now