A little primer on VB6 database programming experience __.net

A long time ago wrote a text, in the floppy disk for n years. --Philips floppy disk quality is good:) aphismail-job#yahoo.com.cn

I learn VB has two or three years of time, beginners, encountered a lot of difficulties, no less in the BBS to ask questions, got a lot of help. Now many beginners like I have encountered a variety of difficulties, and many of which I have encountered. So I moved a thought, the experience of my two years to write a bit, for beginners to reference. Of course, I have no way to write too much, for the time being a basic understanding of the database connection to write a bit. Here is only a little bit of entry-level stuff that is necessary for VB database programming (most of the problems I have encountered when I was a beginner), want to see in-depth things do not waste time here, hehe:)
I only use access to write a few stand-alone program, not what experience, and I do not learn vb is very good, it is possible to appear a variety of errors, welcome to criticize correct.

DAO Section :
A First we need to know where the database is stored.
Connect to the database, often fill in the path of the database, we can use a variable to save the database path. Suppose that the database and the program are stored in the same folder, you can use App.Path to get its path. Note that when the program is placed in the root directory (for example, the C packing directory), App.Path returns "c:/", when the program is placed under a sub-folder (such as c:/db), then return "c:/db", and finally a symbol "/" is missing. I usually use the following methods to solve.
Example 1:
Dim DatabasePath as String
DatabasePath = App.Path
If Right (DatabasePath, 1) <> "/" Then
DatabasePath = DatabasePath & "/"
End If
DatabasePath = DatabasePath & "Mydb.mdb"

Example 2:
Dim DatabasePath as String
DatabasePath = Replace (App.Path & "/mydb.mdb", "//", "/")
two. Connecting a ACCESS97 database with a password with the data control
I on the VB class, the teacher did not talk about this problem, the general book also do not mention, it can be said that I encountered the first problem. To solve this problem, spent a whole day on the Internet to find the answer. You do not laugh, at that time I do not know that there is a website called csdn, do not know Google, also rarely on BBS, so find a particularly strenuous.
The method is simple, select the data control and fill in the password in the Properties window's Connect bar. If the password is "123", fill it out; pwd=123 (be careful not to discard the preceding semicolon). Fill in the full path of the database in the DatabaseName column and select the table in the RecordSource column. Ok.
three. Connecting a Access2000 database with a password with the data control
First, install Visual Basic 6.0 Service Pack 5 (SP5), or do not expect to connect to the Access2000 library (which displays the error message "Unrecognized database format"). This thing has more than 100 m, the Microsoft website is available for download. Before installing SP5, you need to upgrade MDAC to 2.5. MDAC2.5 can also be downloaded from the Microsoft website, and it seems that the SP5 contains this stuff (it's unclear). After downloading, run Mdac_typ.exe to upgrade MDAC, and then run the Setupsp5.exe installation SP5.
Connect Access2000 with the data control you cannot fill in the Properties window with a password, write code, and cannot write in the Form_Load event, otherwise the error message "Unrecognized database format" is still displayed. You might consider writing this code in the Form_Activate event.
Cases:
Data1.databasename = "E:/system.mdb"
Data1.connect = ";p wd=123"
Data1.recordsource = "Employee Table"
Data1.refresh
Four. Leave the data control, connect the database with code
DAO programming, does not allow the Datasouce property of a bound control to be set in code. Therefore, instead of using the data control, you will not be able to implement binding to the control, in exchange for greater flexibility and functionality.
First add a reference to DAO: Menu "Engineering", "References", if you connect the ACCESS97 library, select Microsoft DAO 3.51 Object Library, if you connect Access2000 library, select Microsoft DAO 3.6 Object Library. A simpler approach: place a Data control on the window and delete it, and VB will automatically add a reference to the DAO.
Cases:
Dim DB as DAO. Database
Dim tbl as DAO. TableDef
Dim rs as DAO. Recordset
Set db = OpenDatabase ("E:/system.mdb", False, False, ";p wd=123")
Set TBL = db. TableDefs ("professional")
Set rs = tbl. OpenRecordset ()
Do and not Rs. Eof
Debug.Print Rs. Fields ("Professional name")
Rs. MoveNext
Loop
Note: The OpenDatabase function has the middle two parameters, the first one is the exclusive flag, the second is the read-only flag. In this example, the database is opened in shared, read-write mode.
Five. Compress database
Access database, where useless records are deleted, still occupy disk space. After a long period of use, the database becomes bloated and must be compressed to improve efficiency. We can open the Access software, click "Tools", "Database Utilities", "Compact and Repair Database", to compress the database. You can also write code in your program to achieve the same effect.
All the books, any mention of this function, all told one thing: the compressed database name, must not be the same as the original database. Because if the compression fails, Jet will delete the compressed database, the same name, the consequences would be disastrous. The result of my actual experiment is that if the two have the same name, run-time error 3204 will appear: "Database already exists", will not give you compression, nature is not as dangerous as the book said.
This is a bit of a risky job, pay attention to error capture.
Cases:
Dbengine.compactdatabase "E:/system.mdb", "E:/system1.mdb",,, ";p wd=123"
Kill "E:/system.mdb"
Name "E:/system1.mdb" as "E:/system.mdb"
If you want to compress the database while changing the password, for example change "123" to "456", the first sentence should be changed to
Dbengine.compactdatabase "E:/system.mdb", "E:/system1.mdb", ";p wd=456",, ";p wd=123"
If you wish to remove the password, the first sentence should read
Dbengine.compactdatabase "E:/system.mdb", "E:/system1.mdb", ";p wd=",, ";p wd=123"
Six. Change Database Password
Dim DB as DAO. Database
Set db = OpenDatabase ("E:/system.mdb", True, False, ";p wd=123")
Db. NewPassword "123", "456"
Seven. Get the names of all the tables in the database
Dim DB as DAO. Database
Set db = OpenDatabase ("E:/system.mdb", False, False, ";p wd=123")
Dim I as Long
For i = 0 to db. Tabledefs.count-1
Debug.Print db. TableDefs (i). Name
Next
Eight. Execution of SQL statements
I just learned VB database programming time, read two related books. The book voluminous the function of various SQL statements, syntax, but did not introduce how to use these SQL statements in VB, make even very depressed. Hope later comrades do not go through this period of depression.
1. For the SELECT statement used to return the recordset, you can use it as follows
Example 1:
Data1.databasename = "E:/system.mdb"
Data1.connect = ";p wd=123"
Data1.recordsource = "SELECT * from professional where professional name <> ' 123 '"
Data1.refresh
Do and not Data1.Recordset.EOF
Debug.Print Data1.Recordset.Fields ("Professional name")
Data1.Recordset.MoveNext
Loop
Note: "SELECT * from professional where professional name <> ' 123 '" can also be written in the Recordsouce property bar of the data control (with no double quotes on either side).
Example 2:
Dim DB as DAO. Database
Dim rs as DAO. Recordset
Set db = OpenDatabase ("E:/system.mdb", False, False, ";p wd=123")
Set rs = db. OpenRecordset ("SELECT * from professional where professional name <> ' 123 '")
Do and not Rs. Eof
Debug.Print Rs. Fields ("Professional name")
Rs. MoveNext
Loop
2. For SQL statements that do not return recordsets, the following methods are performed:
Example 1:
Data1.databasename = "E:/system.mdb"
Data1.connect = ";p wd=123"
Data1.refresh
Data1.Database.Execute "Delete * from professional where professional name = ' 123 '"
Example 2:
Dim DB as DAO. Database
Set db = OpenDatabase ("E:/system.mdb", False, False, ";p wd=123")
Db. Execute "Update Professional set professional name = ' 123 ' where professional name = ' Foreign Trade English '"
Nine Packaged
The program is finished, if you do not use it, it is unavoidable to play an installation package. VB provides a packaged tool package & Deployment Wizard that is easy to get started with. One of the problems that novices often encounter is that they don't know how to add database files to the installation package.
Very simple, run the Pack and Go Wizard to the "Include Files" step, click the "Add" button, your database files are included in the line. In the installation location step, you can set the installation path for the database. If you want to put the database and the program in the same directory, to adopt its default value. If you want to put in a folder lower than the program, such as the database folder, you can set the installation location to "$ (AppPath)/database."

ADO Section:
A Connecting the database with the ADODC control
Cases:
adodc1.connectionstring = "Dbq=e:/system.mdb;driver={microsoft Access Driver (*.mdb)};p wd=123;"
Adodc1.recordsource = "SELECT * from professional where professional name <> ' 123 '"
Adodc1.refresh
Set Text1.datasource = ADODC1
Text1.datafield = "Professional name"
two. Connect to the database without using the ADODC control
ADODC controls are easy to use, but they are limited in functionality and slow to use. With ADO, you can dynamically change the Datasouce property of a bound control in your code, so a control can be bound to a Recordset object, not necessarily bound to a adodc control, which is much more convenient than DAO. I think it further weakens the need to use the ADODC control (actually I never really used the ADODC control).
First, add a reference to ADO, the menu "project", "Reference"->microsoft ActiveX Data Objects 2.5 Library (not necessarily 2.5,2.1). You can also have VB add this reference automatically by placing a adodc control on the form.
Example 1: This example does not use a DSN-based connection string as the replacement method, specifying the ODBC driver. Specifying a DSN requires that the ODBC data source be defined through the Windows Control Panel, which is a bit cumbersome, and future packaging installs are a problem.
Dim cn as New ADODB. Connection
Dim rs as New ADODB. Recordset
cn. Open "Dbq=e:/system.mdb;driver={microsoft Access Driver (*.mdb)};p wd=123;"
Rs. Open "Professional", CN, adOpenDynamic, adLockReadOnly, adCmdTable
Set Text1.datasource = rs
Text1.datafield = "Professional name"
Example 2: Using the OLE DB interface is preferred, much faster than ODBC. As MSDN puts it, "the ideal environment for ADO or RDS programmers is that each data source has an OLE DB interface so that ADO can invoke the data source directly." Access certainly provides an OLE DB interface, so let ODBC go a little farther:)
Dim cn as New ADODB. Connection
Dim rs as New ADODB. Recordset
cn. Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=e:/system.mdb; Jet oledb:database password=123; "
Rs. Open "SELECT * from professional where professional name <> ' 123 '", CN, adOpenKeyset, adLockReadOnly, adCmdText
Do and not Rs. Eof
Debug.Print Rs. Fields ("Professional name")
Rs. MoveNext
Loop
three. Get the names of all the tables in the database
Use ADO to get all the table names, not as direct as DAO, so many people do not know how to do. I was also one day idle bored, a mess to do a half-day test, accidental discovery of this stuff.
First, add two references: Microsoft ActiveX Data Objects 2.5 Library and Microsoft ADO Ext. 2.5 for DDL and Security
Cases:
Dim cn as New ADODB. Connection
Dim x as New ADOX. Catalog
cn. Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=e:/system.mdb; Jet oledb:database password=123; "
X.activeconnection = cn
Dim I as Long
For i = 0 to X.tables.count-1
Debug.Print X.tables (i). Name, X.tables (i). Type
Next
Four. Compress database
At the beginning of programming with ADO, do not know how to implement database compression. Then accidentally in a book to see an example, hurriedly remember. Strangely, since then, it seems like everywhere you can see an example of compressing a database with ADO:)
First, add references to Microsoft Jet and Replication Objects 2.1 Library and Microsoft ActiveX Data Objects 2.1 Library
Cases:
Dim JJ as New JRO. JetEngine
Jj.compactdatabase "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=e:/system.mdb; Jet oledb:database password=123; "," Provider=Microsoft.Jet.OLEDB.4.0;Data Source=e:/system1.mdb; Jet oledb:database password=123; "
Kill "E:/system.mdb"
Name "E:/system1.mdb" as "E:/system.mdb"
Note: This method simultaneously implements the repair to the database, also can realize the change of the password. The methods that are specifically used to modify passwords in ADO are not yet found.
Five. Storing pictures in the database
In ADO, it is more troublesome to store images in a database than DAO, and it is not possible to automatically save images in a bound picture frame into a database. Prior to version 2.1, ADO programmers typically do this by AppendChunk, which is not a common problem, and the MSDN example has more than 50 lines. So a lot of people simply store the path of the image in the database, and then loadpicture the display.
By the 2.5 version, ADO provided a stream to solve this problem, very convenient.
This method, which I see in a book, says that although value is the default attribute of field, it cannot be omitted here. I tried it, even if I didn't write it. Value, which uses the default property and works correctly. In this case, however, the Value property is explicitly indicated.
First, add a reference to the Microsoft ActiveX Data Objects 2.5 Library
Example 1: Storing a picture file in the database
Dim cn as New ADODB. Connection
cn. Open "Dbq=e:/db.mdb;driver={microsoft Access Driver (*.mdb)};"
Dim s as New ADODB. Stream
Dim rs as New ADODB. Recordset
Rs. Open "Picture table", CN, adOpenDynamic, adLockOptimistic, adCmdTable
S.type = adTypeBinary
S.open
S.loadfromfile "E:/camcord.bmp"
Rs. AddNew
Rs. Fields ("image"). Value = S.read ()
Rs. Update
Example 2: Exporting image data from a database to a file
Dim cn as New ADODB. Connection
cn. Open "Dbq=e:/db.mdb;driver={microsoft Access Driver (*.mdb)};"
Dim s as New ADODB. Stream
Dim rs as New ADODB. Recordset
Rs. Open "Picture table", CN, adOpenDynamic, adLockOptimistic, adCmdTable
S.type = adTypeBinary
S.open
S.write Rs. Fields ("image"). Value
S.savetofile "E:/xxx.bmp"
Note: 1. The image field type of the picture table is an OLE object.
2. As can be seen, this approach is not only used to store picture files, but virtually any document can be stored in the database in this way, just as there is no need to do so. In fact, if a large number of pictures, after the data is stored, the browsing speed will become very slow, instead of storing the file path.