General discussion

Locked

VB 6.0 front-end to Access 2000

By donq ·
I have a working Access 2000 application I want to convert into an executable using VB6.0. My Operating System is Windows 2000 and my VB comes from Visual Studio 6 w/SP-3 installed. I want Version 1.1 of my project named ?BNM? to link to an Accessback-end employing Microsoft?s DAO 3.6 Object Library. I don?t want to build this executable with the Access Developers Toolkit ? I need to learn pure VB and want to it managing objects I understand.

I am having trouble declaring and loading thelink to BNM.mdb (my Microsoft Access 2000 application containing the tables, queries, forms, reports, and modules) into my VB Project named BNM. I have created a ?BNM? directory on my C drive where I store BNM.mdb (C:\DataByte\BNM\BNM.mdb) and the Visual Basic Project (C:\DataByte\BNM\BNM.vbp).

I installed the Microsoft DAO 3.6 Object Library (via Project | References) and renamed Form1 to ?frmDesktop?. My new VB Project hierarchy reads ?BNM(BNM.vbp)? and it has a Forms collection containing ?frmDesktop(Desktop.frm)?.

Where do I declare and place the code:

Dim db As Database

Set db = OpenDatabase(?C:\DataByte\BNM\BNM.mdb?)

I need one or more of my 12-15 tables available to every object I create within the BNM Visual Basic 6 project so I can write unique query (OpenRecordset) to each form?s load event?

What is my next step to be able to see (and manage) my Access database objects within the VB environment on my forms or through the Object Browser?

How do I efficiently convert my Access Forms to VB Forms (without re-constructing everything)?

This conversation is currently closed to new comments.

3 total posts (Page 1 of 1)  
| Thread display: Collapse - | Expand +

All Comments

Collapse -

VB 6.0 front-end to Access 2000

I would put the code in each function you use to access your database.
It's not a good idea to keep the database open all the time. This can cause problems with db corruption.
You should also use the DAO workspace object's transaction methods whenwriting to tables.
Example:
Dim blnInTrans As Boolean 'transaction flag
blnInTrans = False 'no transaction

Dim lngCantProceed As Long 'used to check return of functions 'and to check errors

Dim ws As DAO.Workspace 'for transactions
Dim db As DAO.Database 'current database
Dim rs As DAO.Recordset 'recordset to be updated (no records)
Set ws = DBEngine(0)
Set db = ws(0)
Set rs = db.OpenRecordset(cCorrMainAddRecSQL, dbOpenDynaset)

'begin transaction and set flag
ws.BeginTrans
blnInTrans = True

'attempt to add record
With rs
'Proceed if no records in recordset
If .EOF And .BOF Then
!StationCode = frm!StationCode
!Ref = frm!Ref
!Year = frm!Year
!ItemDate = frm!ItemDate
!TransCode = frm!TransCode
!DayItemNo = GetDayItemNo(frm!StationCode, frm!Ref, frm!Year)
!Comments = frm!Comments
lngCantProceed = WriteUserInfo(rs, moUserTypes.CreatingUser)
If lngCantProceed <> 0 Then
Err.Raise lngCantProceed
End If
Else
'FATAL ERROR no records should have been returned
Err.Raise moErrors.moTooManyRecs
End If
End With

'commit the transaction and clear the flag
ws.CommitTrans
blnInTrans = False

Collapse -

VB 6.0 front-end to Access 2000

CORRECTION!!!!!

The record write loop should read
'Proceed if no records in recordset
If .EOF And .BOF Then
.AddNew 'OMITTED
!StationCode = frm!StationCode
!Ref = frm!Ref
!Year = frm!Year
!ItemDate = frm!ItemDate
!TransCode = frm!TransCode
!DayItemNo = GetDayItemNo(frm!StationCode, frm!Ref, frm!Year)
!Comments = frm!Comments
lngCantProceed = WriteUserInfo(rs, moUserTypes.CreatingUser)
If lngCantProceed <> 0 Then
Err.Raise lngCantProceed
End If
.Update 'OMITTED
Else

See what happens if you do things in a hurry!

The constant used to open the recordset returns no records. (This definition will be moved to a table before release.)
The WriteUserInfo function adds current user and date info to the recordset.
If you need further help, e-mail me.
John

Collapse -

VB 6.0 front-end to Access 2000

by donq In reply to VB 6.0 front-end to Acces ...

I have re-worded my question and revised my target. I need to move some VB functionality into Access - not visa-versa as I initially stated. Upped the points to 2K to make it worth while also but need somethine I can make-work before approving.

Back to Web Development Forum
3 total posts (Page 1 of 1)  

Related Discussions

Related Forums