Visual Basic 6 & Access 2000

**Demon** · - 23rd August 2006, 16:54

Not really 'cause I'm using Access to store the data. I'll have tons of questions on that too so I thought this would be a nice place to put them.

Robert

**rhino** · - 23rd August 2006, 18:07

One cool thing I've done in the past is utilized stored queries to minimize code. These are similar to stored procedures if you utilize Oracle. You basically create a common sql statement in access, then call that in your vb app (ASP in my case) with the parameters required for the stored query. Here's an example of what I've done.... keep in mind it is for an ASP webpage, but should be similar for VB.

Code:

*****************************************************
<%
	' -- InsertProc.asp --
%>
<html>
<head>
	<title>Running Stored Procedures in Access Database (Insert Value)</title>
    <style>p { font-family:verdana,arial; font-size:10pt; font-weight:bold; }</style>
</head>
<body><p>
<%	
	' Connection String
	Dim connStr
		connStr = "DRIVER=Microsoft Access Driver (*.mdb);DBQ="
		connStr = connStr & Server.MapPath("StoredProc.mdb") & ";PWD=Whateveryourpasswordis"

		
	' Connection Object
	Dim con
		Set con = Server.CreateObject("ADODB.Connection")
		
	' Recordset Object
	Dim rs
		
		' connecting to database
		con.Open connStr
Here we execute the insertproc query
		' executing stored procedure
		Set rs = con.Execute ("exec InsertProc XXX") ' XXX is the data passed to the stored query
Now run another stored query called SelectProc               		
		Set rs = con.Execute ("exec SelectProc")
		
		' showing all records %>
The code below just writes the table data to the web page
		<table border="1" width="25%">
		<%for each x in rs.Fields ' Write the field names in the table headers
			response.write("<th>" & x.name & "</th>")
		next%>
		</tr>

		<%do until rs.EOF ' Read thru each record %>
    		<tr>

    		<%for each x in rs.Fields ' Put the value of each field of the current record into the table 
       			Response.Write "<td>" & (x.value) & "</td>"
    		next

    		rs.MoveNext ' Move to the next record %>
    		</tr>
		<%loop
		' Close connections and release the objects in memory	
		con.Close
		Set rs = Nothing
		Set con = Nothing


%>
</p></body>
</html>
*************************************************

The access query is named InsertProc. And the query code looks like this....

Code:

INSERT INTO [Names] ( Name )
VALUES ([@newName]);

This simple query inserts the value you pass into Table Names into Field Name.
The code for the SelectProc is this....

Code:

SELECT *
FROM [Names];

Anyway.... Thought this could be handy for others.

**Demon** · - 23rd August 2006, 23:53

Here's an example of emptying a database and adding one record:

Dim dbMenu As Database
Dim rsMenu As Recordset

Set dbMenu = OpenDatabase("E:\EasyHID\Xk1 v2\USBProject\VisualBASIC\Modified code\NR2003.mdb")
Set rsMenu = dbMenu.OpenRecordset("SELECT * FROM tblMenu")

If Not rsMenu.EOF Then
rsMenu.Delete
End If

rsMenu.AddNew
rsMenu!ptrMenu = 1
rsMenu!RecordType = "1"
rsMenu.Update

Robert

**Luciano** · - 24th August 2006, 08:56

Hi Robert,

First you will have to decide which technology to use
to access the Microsoft Jet database engine.

The two options you have with VB 6.0 are:

Microsoft Data Access Objects (DAO)
Microsoft ActiveX Data Objects (ADO)

Once decided, stick with one!

My two cents advice is to use ADO.

* * *

Are you sure that the users of this forum
are really interested in this stuff?

There are hundred of forums and tutorials talking
about VB, so why not search and subscribe there?

Best regards,

Luciano

**rhino** · - 24th August 2006, 14:32

Originally Posted by Luciano

Are you sure that the users of this forum
are really interested in this stuff?

There are hundred of forums and tutorials talking
about VB, so why not search and subscribe there?

Isn't that why it's posted in the OFF TOPIC forum?

**Demon** · - 24th August 2006, 15:50

Search: Key Word(s): vb

Showing results 1 to 25 of 134

I think that explains why this thread is justified.

**mister_e** · - 25th August 2006, 03:34

There is many Programming forum like the nice www.experts-exchange.com/ but they're not really hardware oriented... well those i know so far.

http://www.programmersheaven.com/ is another great source.

Or 1 Million in my bank account will be 'tha solution' LMAO!

Thread: Visual Basic 6 & Access 2000

Thread Tools

Search Thread

Display

Hybrid View

Stored Queries

Similar Threads

SerialPort access in visual basic express ed

Visual Basic 6 question

Visual Basic 6 and Pic com

Output PIC module

'877 and Visual Basic 6

Members who have read this thread : 0

Bookmarks

Bookmarks

Posting Permissions