Search VB 2010

Showing posts with label VB 2010 MS-Access 2003/2007 ADO.Net. Show all posts
Showing posts with label VB 2010 MS-Access 2003/2007 ADO.Net. Show all posts

Sunday, May 01, 2016

VB 2010 with MS Access 2010 Error Conversion from type 'DBNull' to type 'String' is not valid.

Conversion from type 'DBNull' to type 'String' is not valid.
Error :
Conversion from type 'DBNull' to type 'String' is not valid.
Cause :
Trying to Fetch Data From empty database table field
Solution :


Here are some online Visual Basic lessons and courses :

Tuesday, April 14, 2015

VB 2010 MS Access Compact and Repair Database



MS Access 2003/2007/2010 Compact and Repair

VB.Net Compact and Repair Database - MS Access

Neither ActiveX Data Objects (ADO) nor ADO .NET provide the means to compact or repair Microsoft Access databases. However, you can accomplish this task by using the Microsoft Jet OLE DB Provider and Replication Objects (JRO) that was introduced with Microsoft Data Access Components (MDAC) version 2.1. ADO .NET allows the use of COM-based object libraries through the Interop layer.

This article demonstrates how to compact an Access database by using Visual Basic .NET.

Requirements

  • Microsoft Visual Basic .NET
  • Microsoft Jet and Replication Objects 2.1, 2.5, or 2.6 Library

Steps to build example

  1. Open a new Visual Basic .NET console application.
  2. In the Solution Explorer window, right-click the References node and select Add Reference.
  3. In the Add Reference dialog box, click the COM tab, and then select Microsoft Jet and Replication Objects 2.x Library. Click Select to add it to Selected Components. Click OK.

    Note In this step, x is a placeholder for the actual version number of the Microsoft Jet and Replication Objects Library component.
  4. A warning will be displayed if there is no wrapper found for the selected library. Click Yes to generate a wrapper. Microsoft ActiveX Data Objects Library (ADODB) and JRO references will be added to the project's References.
  5. In the Solution Explorer window, right-click Module1.vb and click View Code.
  6. Delete all of the code from the code window.
  7. Copy the following code and paste it into the code window: 
  8.  

     
  1. Change the path to the Source and Destination .mdb files as appropriate. Press F5 to build and run the project.

    The compacted database will be in Access 2000 (Jet 4.0) format. For a different Jet format, see "References."

Pitfalls

In order to compact a database, the Jet Database Engine requires exclusive access to the database file. Attempting to compact a database file that is currently in use will result in an exception. This exception can be caught using a Try...Catch structure.

Wednesday, June 12, 2013

Visual Basic Online Course - The Microsoft.ACE.OLEDB.12.0 provider is not registered on the local machine

The Microsoft.ACE.OLEDB.12.0 provider is not registered on the local machine

MS-Access 2010 for VB 2010
MS-Access 2010 for VB 2010
Visual Basic 2010 Error Message :
InvalidOperationException was unhandled
" The Microsoft.ACE.OLEDB.12.0 provider is not registered on the local machine" ?
This error message means that the machine [Computer, Laptop] that you are trying to run your application on, doesn't have MS-Access 2007 installed, or not installed properly. Some developers just pack their applications and send it to the client :
- relying on - that the client have MS-Office 2007/2010 installed already and won't never crash or updated to 2010, 
- or they just did not include the proper component along with their package, 
- or even worse they may not know what exactly should they do to make it work on the client's machine.

╚Ž╣ To get this error message to go away, you need to include this file in your package :

2007 Microsoft Office System Update: Redistributable Primary Interop Assemblies

 Some think that Office 2007 should be installed on a client's machine in order for the application to run clean or properly with any bugs, No, you can include this tool for both [Win32 and Win64] and don't count the client to have Office installed or not.

Get the file from here [AccessDatabaseEngine] for both Win64bit and Win32bit, available in all languages supported by Microsoft. Here are some information about downloading and installing the tool :

When you first click on Download on the Download page, you will see :

Choose the download you want

File Name

AccessDatabaseEngine.exe    25.6 MB
AccessDatabaseEngine_x64.exe    28.1 MB

The download should start for selected file ...
Office

Thank you for downloading

The 2007 Microsoft Office system Primary Interop Assemblies (PIA) redistributable is a Microsoft Windows Installer package that contains the Primary Interop Assemblies for 2007 Microsoft Office system products.

 You could see a reference to Office 2010, don't panic, this is the same, it's the same tool that supports also Office 2010. If your application relies on MS-Access 2010 Database and you got the same meaningful Error message then the same cause needs to be fixed by including this very same file into your package without relying on the client's machine to have MS-Office 2010 or MS-Access 2010 installed on it.

Thank you for downloading

  • Microsoft Access Database Engine 2010 Redistributable

    After the download finishes, pick up the corresponding file that meets your OS kind [32 or 64]

    ♥ Installation Instructions ♥

    To install this download:
    1. Download the file by clicking the Download button and saving the file to your hard disk.
    2. Double-click the AccessDatabaseEngine.exe program file on your hard disk to start the setup program.
    3. Follow the instructions on the screen to complete the installation.
    To use this download:
    1. If you are the user of an application, consult your application documentation for details on how to use the appropriate driver.
    2. If you are an application developer using OLEDB, set the Provider argument of the ConnectionString property to “Microsoft.ACE.OLEDB.12.0”

      If you are connecting to Microsoft Office Excel data, add the appropriate Extended Properties of the OLEDB connection string based on the Excel file type:

      File Type (extension)                                             Extended Properties
      ---------------------------------------------------------------------------------------------
      Excel 97-2003 Workbook (.xls)                                  "Excel 8.0"
      Excel 2007-2010 Workbook (.xlsx)                             "Excel 12.0 Xml"
      Excel 2007-2010 Macro-enabled workbook (.xlsm)      "Excel 12.0 Macro"
      Excel 2007-2010 Non-XML binary workbook (.xlsb)      "Excel 12.0"

    3. If you are an application developer using ODBC to connect to Microsoft Office Access data, set the Connection String to “Driver={Microsoft Access Driver (*.mdb, *.accdb)};DBQ=path to mdb/accdb file”
    4. If you are an application developer using ODBC to connect to Microsoft Office Excel data, set the Connection String to “Driver={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};DBQ=path to xls/xlsx/xlsm/xlsb file”
    To remove this download:
    To remove the download file itself, delete the file AccessDatabaseEngine.exe
    1. On the Start menu, point to Settings and then click Control Panel.
    2. Double-click Add/Remove Programs.
    3. In the list of currently installed programs, select “Microsoft Access database engine 2010” and then click Remove or Add/Remove. If a dialog box appears, follow the instructions to remove the program.
    4. Click Yes or OK to confirm that you want to remove the program.  

     

    Additional Information


      This package contains components that are at the same version as the “The 2007 Microsoft Office Suite Service Pack 1 (SP1)” release.

      The Office System Drivers are only supported under certain scenarios, including:

      1. Desktop applications which read from and write to various files formats including Microsoft Office Access, Microsoft Office Excel and text files.
      2. To transfer data between supported file formats and a database repository, such as SQL Server. For example, to transfer data from an Excel workbook into SQL Server using the SQL Server Import and Export Wizard or SQL Server Integration Services (provided the SSIS jobs run in the context of a logged-on user with a valid HKEY_CURRENT_USER registry hive).
 Source for Installation instruction (Microsoft)

Here are some online Visual Basic lessons and courses :

Thursday, November 25, 2010

Visual Basic 2010 with MS-Access Database

Understanding ADO .Net for Visual Basic 2010 and Microsoft Access 2003 / 2007 Database

Visual Basic .Net and Ado.Net for MS-Access Database course online
There are two methods to work with MS-Access 2003/2007 Database (*.Mdb / *.Accdb) from Visual Basic 2010 using ADO .Net technology .
With .NET, Microsoft has released a new mechanism for accessing data: ADO.NET. The name is a carryover from Microsoft's ADO (ActiveX Data Objects) technology, but it no longer stands for ActiveX Data Objects--it's just ADO.NET. To avoid confusion, I will refer to ADO.NET as ADO.NET and to ADO as classic ADO.
If you're familiar with classic ADO, be careful--ADO.NET is not a descendant, it's a new technology. In order to support the Internet evolution, ADO.NET is highly focused on disconnected data and on the ability for anything to be a source of data. While you will find many concepts in ADO.NET to be similar to concepts in classic ADO, it is not the same.

Method (1) Online mode
Online-mode is just like the same old method we used to work with Databases from Visual Basic 6.0 and ADO2.8 version, all you need to do is :
    • Create Access Database and a Table with your fields.
    • Define the Database path
    • Include ADO2.8 Reference into the Visual Basic 6.0 project
    • Connect the Database
    • Open the Database
    • Operate your events (Save, Edit, Delete and find)
    • Exit the VB6 Project and you will find the data in the Table were affected if events were processed correctly in the VB6 Project.
 And the this is the same logic with ADO.Net in Visual Basic 2010 and MS-Access 2003/2007. The difference is in coding interface, but also the logic is the same some how, more than VB6 Coding, but more understandable.

An example to the Online-mode with MS-Access 2003, is the Friends_Info. Basic code to connect to a MS-Access Database  2003/2007 from Visual Basic 2010 using the ADO.Net Online-Mode is :
Visual Basic Online Courses

♦ Connect to and Open Database :

♦ Save Data into Table :

♦ Search for Data :

♦ Delete Data from Table :



Method (2) Offline mode
ADO.Net Offline Mode

You will see the difference now of using ADO technology only if you are familiar with classic ADO that we used to use in Visual Basic 6.0, but if you did not use it before that you won't.
ADO.Net Offline mode or the Disconnected method of working with Databases has a nice and easy logic :
♦ ADO.Net Offline mode works great with Client/Server applications
ADO.Net method is the preferred way to code client/server applications. The DataSet object is what the ADO.Net offline/Disconnected mode is all about, it gives you the ability to work on the Database components separately (it stores a Database copy in the Memory - RAM of the client machine while working on the Database components), so it gives you the ability to create multiple tables, fill them with data coming from different sources, enforce relationships between pairs of tables, and more.
 ♦ ADO.Net Offline mode is not recommended for ASP.Net
Though the Dataset is a great choice to work with Client/Server applications, but when working for example with ASP.Net live pages, it is a bad choise because the ASP.Net pages don't live long before they finish their lifetime cycle.
The above Friends Info VB.Net project using ADO.Net offline mode basic codes as follow :
Connect to and Open Database :

Save Data into Table :

Search for Data :

Delete Data :

I will discuss working with Crystal Reports for Visual Basic .Net in details in another post, starting Downloading SAP Crystal Reports until Packing SAP Crystal Reports and install it on the client's machine. Keep up !
Another topic about ADO.Net generally, is the ability to Create/Delete/Back-up Database, Add/Modify/Delete/Back-Up  Tables and Queries using the OpenSchema method. Let's see how to do this in Visual Basic .Net 2010.

VB2010 : To Create new Database using ADO.Net, you will need to add new reference to your project to  AdoX (Microsoft ADO Ext. 2.x for Dll and Security) ...
VS2010, ADOX, ADO.Net, MS-Access
Create new Database in Visual Basic 2010
VB2010 : To list all Database files from a Dir. into a TreeView control

VB2010 : To manage Tables using OpenSchema method

 VB2010 : To manage Fields using OpenSchema method

 

Some Visual Basic Exceptions and Solutions Working with MS-Access 2003/2007

Exception: "Dynamic SQL generation for the UpdateCommand is not supported against a SelectCommand that does not return any key Information"
Solution  :

Exception: "Syntax error (missing operator) in query expression ''He's')'.", while trying to Execute INSERT INTO Statement which carries String Values that contain Apostrophe like (He's)"
Solution :


Here are some online Visual Basic lessons and courses :

Visual Basic Courses - Share