Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

connecting VB6 to MS Access 2010

Posted on 2014-03-26
8
Medium Priority
?
2,465 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 36

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 40

Accepted Solution

by:
PatHartman earned 1400 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
Transaction-level recovery for Oracle database

Veeam Explore for Oracle delivers low RTOs and RPOs with agentless transaction log backup and transaction-level recovery of Oracle databases. You can restore the database to a precise point in time, even to a specific transaction.

 

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
 
LVL 36

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 40

Assisted Solution

by:PatHartman
PatHartman earned 1400 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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
Implementing simple internal controls in the Microsoft Access application.
With the advent of Windows 10, Microsoft is pushing a Get Windows 10 icon into the notification area (system tray) of qualifying computers. There are many reasons for wanting to remove this icon. This two-part Experts Exchange video Micro Tutorial s…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
Suggested Courses

580 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