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
Use the following link to download the free Migration Assistant from Microsoft official website.
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
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.
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
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.
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.
Make sure your are included the appropriate references from References window.
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
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.
Now call the procedure
Call CreateTable(“salesentry”,f)
Will create a new table with fields and having Text field type.
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.
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
How to List all data tables from a access .accdb file in VB.net using the ‘GetSchema’ Method. This tutorial is quick access to the problem.
How to add all column names into a listview Column header in VB.Net, here is my complete tutorial for you
