Category: Access

Update null value in Microsoft Access Database


The regular SQL is null update query  may fail while updating Access database. Access DB has some different is null checking system. Put the Is Null statement at the end of the where section may fix the problem.

Here is the query

UPDATE Sales SET Sales.Taxable = 0
 WHERE Sales.Taxable Is Null

How to migrate MS Access database to MS SQL Server 2005 or later


Wanna migrating Access database to MS SQL Server? You have two options; programmatically deal the transaction with the new database or use Microsoft SQL  Server Migration Assistant for Access and migrate the database directly to a newly created database to SQL Server 2005 or later versions

2018-05-01_155514

Before starting with MSSQLMA Note the following

  1. Prepare your database
  2. Check your Access database version, whether it is 32 or 64 database. If it is 32 make sure that you run 32 version of MSSQLMA which can be found under Microsoft SQL  Server Migration Assistant for Access folder.
  3. There are different versions of MSSQLMA available for different versions of the Access database.

Download Migration Assistant for Windows

Use the following link to download the free Migration Assistant from Microsoft official website.

v5.3 , v6.0 , v7.8

Start Migration

Use Migration Wizard to add database and convert, load and migrate functions to finish the process.

Note:

Sometimes you may need to convert your access database to 97/2000 format for easy migration otherwise it will end up with a bunch of errors.

Run appropriate versions of Migration Assistant

Querying external database in vb6 DAO


DAO is one of many ways to access database in VB6. This tutorial tells you how you can access a .mdb database which is not pointed by the DAO Database object.

I have separate Access 2003 .mdb database files, one is pointed by the Database object conn and the other I want to access using the same connection.

SELECT * FROM [File Path][DBNAME].[TABLE]

Connection

Have a look at the connection. [May be you already know, what its look like, this for those not familiar with DAO in VB6]

Dim externalEmp As Recordset
Dim conn As Database
Set conn = OpenDatabase("D:\105443T.mdb")

Querying Records

The conn is now capable of querying any table within the 105443T database.

Say,

Set tr = conn.OpenRecordset("select * from Transactions")
If tr.RecordCount > 0 Then
 tr.MoveFirst
 tr.MoveNext
 tr.MoveFirst
 
 For c = 0 To tr.RecordCount - 1
 Debug.Print tr!date & "--" & tr!dr & "--" & tr!cr
 tr.MoveNext
 Next c
End If

Querying the external DB

Likewise, I can point external Database too with zero changes in conn object. Just specify the [path of file] [.] [data table Name] in place of the table name in the query.

Set externalEmp = conn.OpenRecordset("select * from C:\users\manoj\documents\office.emp")
If externalEmp.RecordCount > 0 Then
 externalEmp.MoveFirst
 externalEmp.MoveNext
 externalEmp.MoveFirst
 
 For c = 0 To externalEmp.RecordCount - 1
 Debug.Print externalEmp!Ename & "--" & externalEmp!edepart & "--" & externalEmp!ebasic
 externalEmp.MoveNext
 Next c
End If

The same is also possible with ADO and other connections.

That’s it.

Copying rows from one Access database to Another using Query


In access database, we can copy rows from one table to another using query. How can we transfer rows from one database to another?

Using the in  keyword in Insert statement we can specify an external database path as follows in a access query

insert into Account_Transactions(Date ,Name ,Type ) in ‘D:\Sherpharma\T.mdb’ select  Account_Transactions.Date,Account_Transactions.Name,Account_Transactions.Type from  Account_Transactions

Here we copy the rows from current database to the T.mdb files. We can also use this query in programming as well.

 

Create Table at run time using DAO connection in Visual Basic 6.0


Using VB6 database connection (DAO) connection we can not only manipulate data from the existing database but also dynamically create new table according to the necessity of the program. This can be achieved using CreateTableDef method of record set object.

Make sure your are included the appropriate references from References window.

Create a new connection 

Dim MedicalShop as Database
Set MedicalShop=opendatabse(GetAppPath()& “shop.mdb”)

Create the Sub for create table

The the sub will utilizes the Tabledef and Fields objects and by using the CreateTabledef command it will create the new table.

Lets add the body of the procedure as follows

Public Sub CreateTable(tbl As String, fl() As String)
Dim tdf As TableDef
Dim fld As Field
Dim f, I
I = 0
Set tdf = MedicalShop.CreateTableDef(tbl)
For Each f In fl
 If f Empty Then
  Set fld = tdf.CreateField(f, dbText)
  tdf.Fields.Append fld
  I = I + 1
 End If
Next
MedicalShop.TableDefs.Append tdf
MedicalShop.Recordsets.Refresh
End Sub

By default the field type is DbText, you can specify each  with different type by passing an addition argument list or a two dimensional array.

Call the CreateTable method

Dim f(3) as String
f(0)=”Entryno”
f(1)=”Name”
f(2)=”amount”
f(3)=”Narration”

Now call the procedure

Call CreateTable(“salesentry”,f)

Will create a new table with fields and having Text field type.

How to add new fields to Access table in VB6


Let me show how to add a new field into an existing access database Table using Visual Basic 6.0.Using the tableDef and Filed object you can create new fields from within the code . 

Firstly, you need to create Database and Recordset object, make sure the DAO access object library is enabled/added to the project.

Create the UpdateTableField Sub

Sub UpdateTableField(tb As String, new_fld As String)Dim CHECK As Boolean
CHECK = False
Dim tbl As TableDef
Dim fld As Field
Dim strName As String
Set tbl = DB.TableDefs(tb)
  For Each fld In tbl.Fields
    If fld.Name = new_fld Then
      CHECK = True
    End If
  Next
If CHECK = False Then
   Set RS = Nothing
   With tbl
   Set fld = .CreateField(new_fld, dbText)
   fld.DefaultValue = “”
   .Fields.Append fld
  MsgBox “new Field Created !, check table”
  End With
Else
  MsgBox “Field already exist !”End If

The ‘TableDefs’ method will fetch the table schema from the database which hold the field information and then fire the checking for table field. dbText hold the default data type. 

Use the sub

Dim DB As Database
Dim RS As Recordset
Private Sub Command1_Click()
Call UpdateTableField(“Table1”, “RoomID2”)
End Sub
Private Sub Form_Initialize()
Set DB = OpenDatabase(“F:\Manoj\Code\DB-AUTO-FIELD-ACCES\db3.mdb”)
End Sub

A new field “RoomID2” will be created in the ‘Table1‘ Table.
Download the Source Code

VB.Net tutorial: How to add all columns to of table into Listview in VB.Net


How to add all column names into a listview Column header in VB.Net, here is my complete tutorial for you