Visual Basic 6 & Access 2000

**Demon** · - 23rd August 2006, 15:40

Hi,

This thread is meant as a meeting place for discussion about VB6 and Access in PIC/USB-related projects.

Keith, this tutorial explains database access much better than the Step By Step or Programmer's Guide books from Microsoft that came with my VB6 package.

http://www.vbexplorer.com/VBExplorer...er_ADO_DAO.asp

Robert

**Luciano** · - 23rd August 2006, 16:46

Hi,

A better title for this thread would be:

Visul Basic 6.0 & ADO/Jet database engine

Best regards,

Luciano

**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!

**Demon** · - 25th August 2006, 04:50

What techniue would you use to store a "cookie" in VB? I set a drive, folder and filename in a form and I'd like to save that value between sessions.

Is there a nice and efficient way of doing this besides writing to a text file? Is there a standard way of doing this? I'd prefer a technique where the user could not get his grubby hands into the file and screw up pointers.

Robert

**Demon** · - 25th August 2006, 04:57

Originally Posted by mister_e

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.

...

The thing is that several of us are going to go through the same steps. It would be a waste of resources to let people have to sprawl all over when all we need are a few good tutorial links and some help on a few questions.

I'm sure there are tons of good places for GSM, GPS and Bluetooth information. What's the difference? This is just another application that will use PIC and PBP. And when you think about it, Windows is EVERYWHERE today, a lot more than the 3 other applications.

Robert

**rhino** · - 25th August 2006, 06:26

Originally Posted by Demon

What techniue would you use to store a "cookie" in VB? I set a drive, folder and filename in a form and I'd like to save that value between sessions.

I use INI files... that's mainly what they're for. Here's a module I use for them. Just add it to your project and use WriteProfileString & FetchProfileString functions to write and read to the INI file.

Code:

'ModINIFunctions.bas
Option Explicit

Private msWinPath As String
Private mlRetLength As Long
Private msDirReturn As String
Private Declare Function GetPrivateProfileString Lib "kernel32" Alias "GetPrivateProfileStringA" (ByVal lpApplicationName As String, ByVal lpKeyName As Any, ByVal lpDefault As String, ByVal lpReturnedString As String, ByVal nSize As Long, ByVal lpFileName As String) As Long
Private Declare Function GetWindowsDirectory Lib "kernel32" Alias "GetWindowsDirectoryA" (ByVal lpBuffer As String, ByVal nSize As Long) As Long
Private Declare Function WritePrivateProfileString Lib "kernel32" Alias "WritePrivateProfileStringA" (ByVal lpApplicationName As String, ByVal lpKeyName As Any, ByVal lpString As Any, ByVal lpFileName As String) As Long


Public Function FetchProfileString(ByVal sINIFile As String, ByVal sApp As String, _
                                   ByVal sKey As String, Optional DefVal As Variant) As String
'Purpose: Reads information from a *.INI file

'Inputs:    sINIFile    String      Name of file INI file including path
'           sApp        String      Name of application section in INI file
'                                   Example [My App]
'           sKey        String      key name under application section
'           DefValue    String      default value of key
'
'           Example   [MyApp]
'                     MyKey = MyValue

'Assumptions:
'           1.  Windows API GetPrivateProfileString function declared with the following statement in the
'               Private Declare Function GetPrivateProfileString Lib "kernel32" Alias "GetPrivateProfileStringA"
'                   (ByVal lpApplicationName As String, ByVal lpKeyName As Any, ByVal lpDefault As String, ByVal lpReturnedString As String, ByVal nSize As Long, ByVal lpFileName As String) As Long
'

'Effect:   None
    
    Dim sReturn As String
    Dim sDefVal As String
    Dim mlReturnLength As Integer
    
    
    If IsMissing(DefVal) Then
        sDefVal = ""
    Else
        sDefVal = CStr(DefVal)
    End If

  '  msDirReturn = Dir(sINIFile)
    
  '  If msDirReturn = "" Then
  '      mlRetLength = WritePrivateProfileString(sApp, sKey, sDefVal, sINIFile)
  '  End If
    
    sReturn = String$(255, 0)
    mlRetLength = GetPrivateProfileString(sApp, sKey, sDefVal, sReturn, 255, sINIFile)
    FetchProfileString = Left$(sReturn, mlRetLength)
    
End Function

Public Function WriteProfileString(ByVal sINIFile As String, ByVal sApp As String, _
                                   ByVal sKey As String, ByVal sVal As String) As Boolean
    
'Purpose: stores information to a *.INI file

'Inputs:    sINIFile    String      Name of file INI file including path
'           sApp        String      Name of application section in INI fil
'                                   Example [My App]
'           sKey        String      key name under application section
'           sValue      String      value of key
'
'           Example   [MyApp]
'                     MyKey = MyValue

'Assumptions:
'           1.  Windows API WritePrivateProfileString function declared with the following statement in the
'               Private Declare Function WritePrivateProfileString Lib "kernel32" Alias "WritePrivateProfileStringA"
'                (ByVal lpApplicationName As String, ByVal lpKeyName As Any, ByVal lpString As Any, ByVal lpFileName As String) As Long
'           2. if *.INI file does not exist this function will create the file

'Effect:   Contents of *.INI file

Dim sReturn As String
    
    mlRetLength = WritePrivateProfileString(sApp, sKey, sVal, sINIFile)
    
End Function

Here's an example of reading in the language value under the Section named SAP in the Pack.ini file.

Code:

   PstrLang = FetchProfileString(App.Path & "/PACK.INI", "SAP", "LANGUAGE", "EN")

Here's an example of how the data in the INI file would look.

Code:

;comments use the semicolon
;In this example parameter sApp =  SAP
;parameter sKey = Language (there can be multiple keys under one app parameter)
;The value returned to PstrLang in the example above is EN
[SAP]
LANGUAGE=EN

This is a really useful way of storing different user preferences etc.
Another method would be writing and reading to the registry. But I'll let you explore that option.

**Demon** · - 25th August 2006, 15:49

Thanks, that looks great.

I'm not too keen on writing to the registry.

Robert

EDIT: Are you sure about this statement?

-----------------------------------------------------
Here's an example of reading in the language value under the Section named SAP in the Pack.ini file.

Code:

PstrLang = FetchProfileString(App.Path & "/PACK.INI", "SAP", "LANGUAGE", "EN")
-----------------------------------------------------

Why read the INI file to find sValue if you are providing it in the statement? This would look more like a Write statement no?

**Demon** · - 27th August 2006, 16:24

This one is really off-topic.

I'd like to copy an Access MDB file from within VB6. I don't even know where to start looking for this. I figure this could be some sort of batch file like we used to be able to do in DOS.

Robert

EDIT: Hmmm: http://builder.com.com/5100-6373-1050078.html CopyFile feature, interesting.

**Demon** · - 27th August 2006, 16:35

I found this:

FileSystemObject.CopyFile "c:\mydocuments\letters\*.doc", "c:\tempfolder\"

But I'm stuck on how to define that FileSystemObject.

Robert

EDIT: Found it, I needed to add a Reference to Microsoft Scripting Runtime (scrrun.dll).

**rhino** · - 27th August 2006, 22:24

Originally Posted by Demon

Why read the INI file to find sValue if you are providing it in the statement? This would look more like a Write statement no?

Look at the FetchProfileString function. You have to provide a default value in this call, then it gets overwritten with what's in the INI file.

**Demon** · - 29th August 2006, 22:42

How do we point to a VB6 public variable from within a SELECT statement?

Set rsProfile = dbProfile.OpenRecordset("SELECT * FROM tblProfile WHERE ptrMenu = ptrCurrentMenu")

Both ptrMenu (primary key) and ptrCurrentMenu are defined as LONG, but it only works when I hardcode a 1 instead of ptrCurrentMenu.

Robert

EDIT: Found it in Programmer's Heaven, thanks Steve: ("SELECT * FROM tblProfile WHERE ptrMenu = " & ptrCurrentMenu)

**Demon** · - 30th August 2006, 02:01

I would have expected this IF statement to trap a Null, but apparently it doesn't work that way. The execution fell right down to the ELSE and executes the next statement. Any idea how to do make it work?

Robert

**keithdoxey** · - 30th August 2006, 09:26

Originally Posted by Demon

I would have expected this IF statement to trap a Null, but apparently it doesn't work that way. The execution fell right down to the ELSE and executes the next statement. Any idea how to do make it work?

Robert

Try "vbNull" instead. (Cant test as I dont have VB on this machine!)

**Luciano** · - 30th August 2006, 09:40

Hi,

Use the Visual Basic function IsNull().

Best regards,

Luciano

**Demon** · - 30th August 2006, 22:36

Thank you!

Robert

**Demon** · - 31st August 2006, 15:47

-----------------------------------------------------
Dim ptrProfile as Long

Dim dbProfile As Database
Set dbProfile = OpenDatabase(strPath & "\" & strFilename)
Dim rsProfile As Recordset
Set rsProfile = dbProfile.OpenRecordset("tblProfile")

rsProfile.AddNew
rsProfile!Description = "bla bla bla"
rsProfile.Update

ptrProfile = rsProfile!ptrMenu <-- Primary key, Autonum
-----------------------------------------------------

According to one of my Access books this should place the new key into ptrProfile but it doesn't.

Am I supposed to set special parameters for update? Like UpdateNow or something like that? I see references to Cursor Types and Record Locks but I can't figure how to use them.

Robert

**Luciano** · - 31st August 2006, 19:40

Hi,

Try that.

Best regards,

Luciano

Code:

rsProfile.AddNew
rsProfile!Description = "bla bla bla"
ptrProfile = rsProfile!ptrMenu 
rsProfile.Update

**Demon** · - 4th September 2006, 00:32

Thanks.

I got the SendKeys sample from the Help file working nicely:

AppActivate Shell("calc.exe", 1)
For I = 1 To 100
SendKeys I & "{+}", True
Next I
SendKeys "=", True
SendKeys "%{F4}", True

How could I modify it so that I don't hardcode the executable? I'd like to be able to chose what running application gets focus.

Robert

**keithdoxey** · - 4th September 2006, 09:33

Have you tried something simple like

dim TargetAppName as String

.....

TargetAppName = "calc.exe"

.....

AppActivate Shell(TargetAppName, 1)

**Luciano** · - 4th September 2006, 12:59

Hi,

Under most circumstances, SendKeys is not recommended in a production environment.
This is because the keystrokes are processed by whichever window is currently active
on the desktop. Obviously this will cause unpredictable behavior in case another app
receives the focus while your code is processing the Sendkeys statement.
If you're unlucky, the keystrokes sent to application may cause all documents
to be deleted or the hard drive to be formatted.
So try to avoid Sendkeys at all cost.

Best regards,

Luciano

**Demon** · - 4th September 2006, 15:02

Luciano, The whole reason for this application is to send keystrokes to an application.

Keith, That's how the Help file does it, I had shrunk it down a bit for testing.

I was hoping there was a sort of 'list' feature of running applications that I could scroll through; something like ALT-TAB. But now that I think more about it, similar applications make the user select the executable (like the Profiler for the Logitech wheel). By forcing focus on a selected executable, I reduce that danger that Luciano brought up.

Robert

**Demon** · - 6th September 2006, 18:58

I ended up storing the executable, at least it works.

Any idea why this is seen by a game:

SendKeys "{F3}", False

But this is not:

SendKeys "{LEFT}", False

The Help file lists the Left Arrow as a special character just like F3, but it doesn't work for some reason.

Robert

**Luciano** · - 6th September 2006, 19:30

Hi,

Games use DirectInput. (DirectX).

Best regards,

Luciano

**Demon** · - 6th September 2006, 19:36

Yes, but games also use keyboard input.

I found several good tutorial links on a gaming site:
http://www.vbtutor.net/vbtutor.html
http://cuinl.tripod.com/tutorials.htm
http://www.vbcode.com/
http://www.planet-source-code.com/

Not sure if they were mentionned above.

One guy in those tutorials recommended using the SendInput feature instead of the "basically broken" SendKeys.

Robert

**Demon** · - 6th September 2006, 20:39

I'm starting to see what people have moaned about MS products. Most of the links I've read relating with SendKeys says that it is not reliable, if it works at all.

This place describes using a more flexible keyboard API:
http://www.vbaccelerator.com/home/VB...PI/article.asp

Still reading, how it's understandable.

Robert

**Luciano** · - 6th September 2006, 21:04

Hi,

Normal windows programs

When a key is pressed, the keyboard driver passes the keystroke
to Windows and the message is saved in the system message queue.
Windows then transfers the message from the system queue to the
queue of the program with the "input focus" where the keystroke
is then processed.

* * *

Games using DirecX

DirectInput (see DirectX) works directly with the keyboard driver.
So when you play, the game does not get keystrokes as a normal
Windows message.

* * *

Be aware that posts in forums can be very old. (Not about VB6 SP6).
Also most of these post are written by people like me and you.

Best regards,

Luciano

**Demon** · - 6th September 2006, 21:35

I'm still not sure how DirectInput games will come into play with my machine. I guess I'll have to cross that bridge when I fall in the water.

I suppose I can learn about DirectInput later and add in code to deal with those games at that point. I'll add a flag right away in my database; Input Type (DirectInput or Keyboard Input).

I tried that guy's demo, pasted the Class Module directly in my project, I called his version of SendKeys with "{LEFT}" and the Left Arrow worked like a charm in my game.

WOOHOO! I'm back on track.

Robert

**Demon** · - 7th September 2006, 04:39

Lots of small debugging completed. I've found out the importance of nulls and properly initializing fields.

One thing is odd, the custom SendKeys logic above works like a charm except for one thing:

^%{DELETE}

I would have expected the Task Manager screen to pop up but apparently it doesn't work that way. Is there some sort of parameter that disables CTRL-ALT-DELETE by default or something like that?

Robert

Thread: Visual Basic 6 & Access 2000

Thread Tools

Search Thread

Display

Visual Basic 6 & Access 2000

Stored Queries

What about INI files?

Select Where

Null not a null

Reading value of Autonum key after insert

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 : 1

Bookmarks

Bookmarks

Posting Permissions