Solved

connecting VB6 to MS Access 2010

Posted on 2014-03-26
8
1,770 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 33

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
Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
LVL 33

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

This article is meant to give a basic understanding of how to use R Sweave as a way to merge LaTeX and R code seamlessly into one presentable document.
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
The viewer will learn how to pass data into a function in C++. This is one step further in using functions. Instead of only printing text onto the console, the function will be able to perform calculations with argumentents given by the user.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

910 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

24 Experts available now in Live!

Get 1:1 Help Now