Solved

connecting VB6 to MS Access 2010

Posted on 2014-03-26
8
1,709 Views
Last Modified: 2014-03-31
Hello Experts,
I have a VB6 application that has been running more than 8 years.
this application connected to MS Access 2002 format.
Our customer now wants to go to Windows 7 x64 and use MS Access 2010
(I think that currently we can use either 32 bit or 64 bit Access)
this application is running a piece of production equipment on a manufacturing floor
and is using Access for the recipe as well as storing test result data

 - I am looking to see if VB6 can even connect to Access 2010
 - If so, what changes are going to be required for the connection?

thanks,
0
Comment
Question by:Adam_930
  • 4
  • 2
  • 2
8 Comments
 
LVL 32

Expert Comment

by:ste5an
ID: 39956049
If you don't use any specific older API, then your application should run without problems (UAC and virtualisation may be an issue when you haven't used the correct XP paths).

Simply deploy your application on a clean Windows 7 x64 test system. Test whether you need the XP compatibility mode.
Use Process Monitor to indentify possible path issues.

Otherwise you may consider using the Windows XP Mode for Windows 7.
0
 
LVL 34

Accepted Solution

by:
PatHartman earned 350 total points
ID: 39956126
Sounds like you are not actually using Access at all.  Access is the rapid application development tool that creates applications.  Jet (.mdb) and ACE (.accdb) are the database engines with which it is closely associated and frequently confused.  Access, the RAD tool, is separate from Jet/ACE the database engines.  The database engines can be installed without Access and are frequently used by other applications when they need a desktop data store.  In fact Jet was installed by Windows because some windows components used it.  I'm not sure if it still is or if they have switched to ACE.  Access does require the appropriate database engine because that is what it uses to store its own objects so if you install Access you also get Jet or ACE.  But Access can use any ODBC compliant database engine for data storage.

If your application is not Access, then you are connecting to the database (Jet in this case) with an ODBC connection.  You should be able to change your ODBC connection to connect to ACE (.accdb).  The ODBC driver you use has to match bit-wise to the version of Access that created it.  As long as you install the 32-bit version of Access, that will install the correct drivers.  Of course, unless you are using Access for something else, you don't need to install it at all.  You only need to install ACE which should be a free download.
0
 

Author Comment

by:Adam_930
ID: 39956422
Pat,
You are correct that my application does not actually use/communicate to Access
Access was used to create the files, the tables, layout, format, etc

My application does not add fields (columns). I just read, create, edit, delete rows
within these already created files.

thanks, I will give this a try
0
 

Author Comment

by:Adam_930
ID: 39957028
Pat,
Here is portion of my current code
I am not sure how I am supposed to add the ACE db engine.


'   --------------------------------------------------------
Public BOMSetupDB As Database
Public BomSetupTable As Recordset
Public Const BOMSetupName = "\Setup_7.mdb"
Public CaldbTblName
Public BomSetupTblName
Public Const BarCode2DSetup = "BarCode2DSetup"
'   --------------------------------------------------------

BomSetupTblName = "BOMSetup"

Set BOMSetupDB = OpenDatabase(VBAppDir & BOMSetupName)
Set BomSetupTable = BOMSetupDB.OpenRecordset(BomSetupTblName, dbOpenDynaset)

    If BomSetupTable.RecordCount > 0 Then
        BomSetupTable.MoveFirst
        bBOMEnable = BomSetupTable.Fields("BOMEnabled")
        For xLoop = 0 To 3
            bProductionLine(xLoop) = BomSetupTable.Fields("productionline" & (xLoop + 1))
            If bProductionLine(xLoop) = True Then
                iProductionLine = xLoop + 1
            End If
        Next xLoop
        strBOMDrive = BomSetupTable.Fields("BOMDrive")
        strBOMDirectory = BomSetupTable.Fields("BOMSubDirectory")
        PLC_IP = BomSetupTable.Fields("PLC_IP")
    End If
BomSetupTable.Close

CaldbTblName = "Cal1"
Set BomSetupTable = BOMSetupDB.OpenRecordset(CaldbTblName, dbOpenDynaset)

BomSetupTable.MoveFirst
    While Not BomSetupTable.EOF
        Select Case BomSetupTable.Fields("EntryName")
            Case "LabelPrinterCommPort"
                iLabelPrinterCommPort = BomSetupTable.Fields("EntryValue")
            Case "PlasticPackFrequency"
                iPlasticPackFreq = BomSetupTable.Fields("EntryValue")
            Case "i2DLabelPrinterCommPort"
                i2DCommPort = BomSetupTable.Fields("EntryValue")
            Case "iPinStampCommPort"
                iPinStampCommPort = BomSetupTable.Fields("EntryValue")
            Case "iPLCCommPort"
                iPLCCommPort = BomSetupTable.Fields("EntryValue")
            Case "iRejectLabelCommPort"
                iRejectLabelCommPort = BomSetupTable.Fields("EntryValue")
        End Select
        BomSetupTable.MoveNext
    Wend
BomSetupTable.Close
BOMSetupDB.Close
0
Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

 
LVL 32

Expert Comment

by:ste5an
ID: 39957061
Just test it...
0
 

Author Comment

by:Adam_930
ID: 39957109
ste5an,
I opened the original Setup_7.mdb file with MS Access 2010
Saved the file to Setup_7-2010.accdb

Changed my constant:
'   Public Const BOMSetupName = "\Setup_7.mdb"
Public Const BOMSetupName = "\Setup_7-2010.accdb"

When I run the application I get the following error:

"Unrecognized database format Setup_7-2010.accdb"

Access 2010 will open the original .mdb file in a 2000 format,
but the goal is to convert to 2007-2010 format if possible.
0
 
LVL 34

Assisted Solution

by:PatHartman
PatHartman earned 350 total points
ID: 39957395
We all work from within Access so we use linked tables and either DAO or ADO to process a recordset in code.  

How do you establish a connection with the database?  It looks like you just specify the database name.  How do you specify what table/query you are using?  Are you using a DSN?  If so, that would need to change.  Try using ADO so you can create a connection string.
0
 

Author Closing Comment

by:Adam_930
ID: 39966284
Created four (4) buttons on a form - cmdConnect
Added Reference Microsoft Active Data Objects 2.8 Library
This allowed me to connect to the different formats
            xLoop = xLoop    ' this is to allow insertion of breakpoints.

Solution Code:
Dim Conn As New ADODB.Connection
Const dbPath = "C:\Work\W7 Testing\AccessDB Testing"
Const dbFile2000 = "\Access2000.mdb"
Const dbFile2003 = "\Access2003.mdb"
Const dbFile2010 = "\Access2010.accdb"
Dim xLoop%
Dim bConnection(5) As Boolean

Private Sub cmdConnect_Click(Index As Integer)

    For xLoop = 0 To cmdConnect.Count - 1
        cmdConnect(xLoop).Enabled = False
    Next xLoop

    Select Case Index
        Case 0
            Conn.ConnectionString = _
                "Provider=Microsoft.Jet.OLEDB.4.0;" & _
                "Persist Security Info=False;" & _
                "Data Source = " & dbPath & dbFile2000
           
            xLoop = xLoop
           
            Conn.Open
           
            xLoop = xLoop
            lblFile.Caption = dbPath & dbFile2000
       
        Case 1
            Conn.ConnectionString = _
                "Provider=Microsoft.Jet.OLEDB.4.0;" & _
                "Persist Security Info=False;" & _
                "Data Source = " & dbPath & dbFile2003
           
            xLoop = xLoop
           
            Conn.Open
       
            xLoop = xLoop
            lblFile.Caption = dbPath & dbFile2003
       
       
       
        Case 2
            Conn.ConnectionString = _
                "Provider=Microsoft.ACE.OLEDB.12.0;" & _
                "Persist Security Info=False;" & _
                "Data Source = " & dbPath & dbFile2010
           
            xLoop = xLoop
           
            Conn.Open
       
            xLoop = xLoop
            lblFile.Caption = dbPath & dbFile2010
       
       
       
        Case 3
       
       
        Case 4
            Conn.Close
            lblFile.Caption = "No File Open"
           
            For xLoop = 0 To cmdConnect.Count - 1
                cmdConnect(xLoop).Enabled = True
            Next xLoop
            cmdConnect(4).Enabled = False
       
    End Select
   
   
    If Index <> 4 Then
        cmdConnect(4).Enabled = True
    End If


End Sub

Private Sub cmdEnd_Click()

    If cmdConnect(4).Enabled = True Then
        Conn.Close
    End If
   
    End

End Sub

Private Sub Form_Load()

    cmdConnect(4).Enabled = False
   
End Sub
0

Featured Post

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

INTRODUCTION The purpose of this document is to demonstrate the Installation and configuration of the Data Protection Manager product. Note that this demonstration was prepared on the basis of Windows OS is 2008 R2 and DPM 2010. DATA PROTECTI…
When we want to run, execute or repeat a statement multiple times, a loop is necessary. This article covers the two types of loops in Python: the while loop and the for loop.
The goal of the tutorial is to teach the user how to use functions in C++. The video will cover how to define functions, how to call functions and how to create functions prototypes. Microsoft Visual C++ 2010 Express will be used as a text editor an…
The viewer will learn how to use the return statement in functions in C++. The video will also teach the user how to pass data to a function and have the function return data back for further processing.

760 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

22 Experts available now in Live!

Get 1:1 Help Now