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
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
Before starting with MSSQLMA Note the following
Prepare your database
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.
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.
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]
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")
The conn is now capable of querying any table within the 105443T database.
Set tr = conn.OpenRecordset("select * from Transactions")
If tr.RecordCount > 0 Then
For c = 0 To tr.RecordCount - 1
Debug.Print tr!date & "--" & tr!dr & "--" & tr!cr
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
For c = 0 To externalEmp.RecordCount - 1
Debug.Print externalEmp!Ename & "--" & externalEmp!edepart & "--" & externalEmp!ebasic
The same is also possible with ADO and other connections.
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
Will create a new table with fields and having Text field type.
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