none
Microsoft Access Database Engine RRS feed

  • Question

  • Hi ther.

    I am having troble conecting an app up to a microsoft access 2007 database.  

    Aperently I nead something called "microsoft access database engine 2007' I can not find a proper download for this.  I am using Visual Studio 2013 on Win 7

    Can some one point me in the corect direction, or am I going about this the wrong way?


    Thursday, January 16, 2020 9:05 PM

Answers

  • Thanks Karen.

    Visual Studio still dont give me the option for Access, only SQL

    Sounds like you might be attempting to use a data wizard (as per below), if that is the case they may of happened if you installed the 64 bit rather than the 32 bit or vise-versa. I would like to add that this approach to working with data is not recommended, instead look towards either DataAdapter approach or connection + command approach, which is best is dependent on work you are doing.


    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    Thursday, January 16, 2020 10:28 PM
    Moderator
  • Here is an example to read data from MS-Access which in this case joins two tables together (the code logic can be used for one table or several tables).

    Imports System.Data.OleDb
    
    Namespace Classes
        Public Class DataOperationsAccess
            Private ConnectionString As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=NorthWind.accdb"
            Public LastException As Exception
    
            Public Function LoadCustomerRecordsUsingDataTable() As DataTable
    
                Dim selectStatement =
                        "SELECT Cust.CustomerIdentifier, CT.ContactTypeIdentifier, Cust.CompanyName, " &
                        "Cust.ContactName, CT.ContactTitle, Cust.Address AS Street, Cust.City, " &
                        "Cust.PostalCode, Cust.Country, Cust.Phone, Cust.ModifiedDate " &
                        "FROM Customers AS Cust INNER JOIN ContactType AS CT ON " &
                        "Cust.ContactTypeIdentifier = CT.ContactTypeIdentifier;"
    
                Dim customerDataTable = New DataTable
    
                Using cn As New OleDbConnection With {.ConnectionString = ConnectionString}
                    Using cmd As New OleDbCommand With {.Connection = cn}
                        Try
                            cmd.CommandText = selectStatement
    
                            cn.Open()
    
                            customerDataTable.Load(cmd.ExecuteReader())
    
                            customerDataTable.Columns("CustomerIdentifier").ColumnMapping = MappingType.Hidden
                            customerDataTable.Columns("ContactTypeIdentifier").ColumnMapping = MappingType.Hidden
                            customerDataTable.Columns("ModifiedDate").ColumnMapping = MappingType.Hidden
    
                        Catch ex As Exception
                            LastException = ex
                        End Try
                    End Using
                End Using
    
                Return customerDataTable
    
            End Function
    
        End Class
    End Namespace

    Then in a form we can load data from the above code in either form load for forum shown event.

    Dim accessOperations As New DataOperationsAccess
    Dim customersFromAccessDataTable As DataTable =
    		accessOperations.LoadCustomerRecordsUsingDataTable()
    
    customersAccessDataGridView.DataSource = customersFromAccessDataTable		

    The above code comes from a large code sample you can view online or download.

    https://github.com/karenpayneoregon/MoveFromAccessToSqlServerWithVisualBasicNet/tree/master/BasicRead

    I have a lot of code samples for VB.NET and MS-Access below yet does not cover everything but you should be able to find something e.g. BasicRead project.

    https://github.com/karenpayneoregon/WorkingWithAccessDatabases

    If you look at these examples and have questions feel free to ask.

    If none of the above is useful and really want to use the dialog I've shown which you can get to work that can end up being a long and drawn out process. First thing to check is you installed the 32 bit driver not the 64 bit driver.


    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    Thursday, January 16, 2020 11:55 PM
    Moderator
  • I have installed the 32bit version and can not get it uninstalled.

    Why uninstall the 32 bit version, that is what you need. I'm running the 32 bit version on two machines, one Windows 10, the other Windows 7.

    And on the other hand, if you don't use the data wizard data access is done via OleDb data provider which is installed with Visual Studio by default.


    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    Friday, January 17, 2020 1:24 PM
    Moderator

All replies

  • Hello,

    The following link will work, once clicking download you have a choice of 32 or 64 bit. It indicates Engine 2010 but works for 2007 also.

    https://www.microsoft.com/en-us/download/details.aspx?id=13255


    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    Thursday, January 16, 2020 9:50 PM
    Moderator
  • Thanks Karen.

    Visual Studio still dont give me the option for Access, only SQL

    Thursday, January 16, 2020 10:09 PM
  • Thanks Karen.

    Visual Studio still dont give me the option for Access, only SQL

    Sounds like you might be attempting to use a data wizard (as per below), if that is the case they may of happened if you installed the 64 bit rather than the 32 bit or vise-versa. I would like to add that this approach to working with data is not recommended, instead look towards either DataAdapter approach or connection + command approach, which is best is dependent on work you are doing.


    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    Thursday, January 16, 2020 10:28 PM
    Moderator
  • The window you showing above does not show up here at all. 

    I chust doing a simple app that need to retreve data, nothing fancy.

    If ther is a nother way to conect pleas help.
    Thursday, January 16, 2020 10:48 PM
  • Here is an example to read data from MS-Access which in this case joins two tables together (the code logic can be used for one table or several tables).

    Imports System.Data.OleDb
    
    Namespace Classes
        Public Class DataOperationsAccess
            Private ConnectionString As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=NorthWind.accdb"
            Public LastException As Exception
    
            Public Function LoadCustomerRecordsUsingDataTable() As DataTable
    
                Dim selectStatement =
                        "SELECT Cust.CustomerIdentifier, CT.ContactTypeIdentifier, Cust.CompanyName, " &
                        "Cust.ContactName, CT.ContactTitle, Cust.Address AS Street, Cust.City, " &
                        "Cust.PostalCode, Cust.Country, Cust.Phone, Cust.ModifiedDate " &
                        "FROM Customers AS Cust INNER JOIN ContactType AS CT ON " &
                        "Cust.ContactTypeIdentifier = CT.ContactTypeIdentifier;"
    
                Dim customerDataTable = New DataTable
    
                Using cn As New OleDbConnection With {.ConnectionString = ConnectionString}
                    Using cmd As New OleDbCommand With {.Connection = cn}
                        Try
                            cmd.CommandText = selectStatement
    
                            cn.Open()
    
                            customerDataTable.Load(cmd.ExecuteReader())
    
                            customerDataTable.Columns("CustomerIdentifier").ColumnMapping = MappingType.Hidden
                            customerDataTable.Columns("ContactTypeIdentifier").ColumnMapping = MappingType.Hidden
                            customerDataTable.Columns("ModifiedDate").ColumnMapping = MappingType.Hidden
    
                        Catch ex As Exception
                            LastException = ex
                        End Try
                    End Using
                End Using
    
                Return customerDataTable
    
            End Function
    
        End Class
    End Namespace

    Then in a form we can load data from the above code in either form load for forum shown event.

    Dim accessOperations As New DataOperationsAccess
    Dim customersFromAccessDataTable As DataTable =
    		accessOperations.LoadCustomerRecordsUsingDataTable()
    
    customersAccessDataGridView.DataSource = customersFromAccessDataTable		

    The above code comes from a large code sample you can view online or download.

    https://github.com/karenpayneoregon/MoveFromAccessToSqlServerWithVisualBasicNet/tree/master/BasicRead

    I have a lot of code samples for VB.NET and MS-Access below yet does not cover everything but you should be able to find something e.g. BasicRead project.

    https://github.com/karenpayneoregon/WorkingWithAccessDatabases

    If you look at these examples and have questions feel free to ask.

    If none of the above is useful and really want to use the dialog I've shown which you can get to work that can end up being a long and drawn out process. First thing to check is you installed the 32 bit driver not the 64 bit driver.


    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    Thursday, January 16, 2020 11:55 PM
    Moderator
  • I have installed the 32bit version and can not get it uninstalled.
    Friday, January 17, 2020 12:59 PM
  • I have installed the 32bit version and can not get it uninstalled.

    Why uninstall the 32 bit version, that is what you need. I'm running the 32 bit version on two machines, one Windows 10, the other Windows 7.

    And on the other hand, if you don't use the data wizard data access is done via OleDb data provider which is installed with Visual Studio by default.


    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    Friday, January 17, 2020 1:24 PM
    Moderator
  • Thanks Karen. 

    I am marking rhis as answerd although I still did not manage.  My inability to concentrate is holding me back, but I'm determent to do this.  

    Thanks for the sampels, it will help a lot I'm sure.  

    Saturday, January 18, 2020 6:13 AM