Solved

connecting VB6 to MS Access 2010

Posted on 2014-03-26
8
2,093 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 2
  • 2
8 Comments
 
LVL 34

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 37

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
Salesforce Has Never Been Easier

Improve and reinforce salesforce training & adoption using WalkMe's digital adoption platform. Start saving on costly employee training by creating fast intuitive Walk-Thrus for Salesforce. Claim your Free Account Now

 

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 34

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 37

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

Increase Agility with Enabled Toolchains

Connect your existing build, deployment, management, monitoring, and collaboration platforms. From Puppet to Chef, HipChat to Slack, ServiceNow to JIRA, Splunk to New Relic and beyond, hand off data between systems to engage the right people.

Connect with xMatters.

Question has a verified solution.

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

This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
The viewer will learn how to user default arguments when defining functions. This method of defining functions will be contrasted with the non-default-argument of defining functions.
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

691 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