Tag Archives: Access

Exporting DB tables to Excel in VB6


It is something like complicated when you want to export reports from reporting objects like List view in VB6. In fact Excel object has provided a way to directly export contents of a table into Excel format in a quick manner using DAO or ADO connection.

To illustrate this we need,

  1. A DAO, ADO database connection
  2. Excel Objects
  3. Loop to print column heading

Connection

You may be familiar with the database connections in VB6, the following is an example of ADO data connection.

Dim adodb_db As New ADODB.Connection
Dim adodb_rs As New ADODB.Recordset

adodb_db.ConnectionString = “Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\emp.mdb;Persist Security Info=False”
adodb_db.Open
adodb_rs.Open “select * from Accounts “, adodb_db, adOpenKeyset

The excel objects

The excel objects are required to create a new Excel file in order to export the rows of a table.

Dim oExcel As Object
Dim oBook As Object
Dim oSheet As Object

The columns

The CopyFromRecordset of Excel object didn’t fetch columns, so we do it manually as follows.

If adodb_rs.RecordCount > 0 Then
adodb_rs.MoveFirst
adodb_rs.MoveNext
adodb_rs.MoveLast
adodb_rs.MoveFirst

‘Start a new workbook in Excel
Set oExcel = CreateObject(“Excel.Application”)
Set oBook = oExcel.Workbooks.Add
Set oSheet = oBook.Worksheets(1)

For c = 0 To adodb_rs.Fields.count – 1
oBook.Worksheets(“Sheet1”).Cells(1, c + 1) = adodb_rs.Fields(c).Name
Next c

End if

Copying the rows to Excel

The Excel Worksheet object’s CopyFromRecordset help you to copy records to excel rows, all you need to specify the cell range.

oBook.Worksheets(“Sheet1”).Range(“A2”).CopyFromRecordset adodb_rs

The complete code

Here is the complete code

Dim oExcel As Object
Dim oBook As Object
Dim oSheet As Object

Dim adodb_db As New ADODB.Connection
Dim adodb_rs As New ADODB.Recordset

adodb_db.ConnectionString = “Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\SherPharma\SIGMA1819.mdb;Persist Security Info=False”
adodb_db.Open
adodb_rs.Open “select * from Account_Transactions “, adodb_db, adOpenKeyset

‘Set rs = MedicalShop.OpenRecordset(“select entryno,itemname,total from Sales_Particulars “)
If adodb_rs.RecordCount > 0 Then
adodb_rs.MoveFirst
adodb_rs.MoveNext
adodb_rs.MoveLast
adodb_rs.MoveFirst

‘Start a new workbook in Excel
Set oExcel = CreateObject(“Excel.Application”)
Set oBook = oExcel.Workbooks.Add
Set oSheet = oBook.Worksheets(1)

For c = 0 To adodb_rs.Fields.count – 1
oBook.Worksheets(“Sheet1”).Cells(1, c + 1) = adodb_rs.Fields(c).Name
Next c
oBook.Worksheets(“Sheet1”).Range(“A2”).CopyFromRecordset adodb_rs

oExcel.Visible = vbTrue

End If

 

Add auto field with alter statement in Access/Vb6


Create auto filed ,which automatically generate value, programmatically in Vb6 is easy with two line of code.

What we will do when you need create auto value programmatically or with query.

Here what you need to do

  1. Create a number/text field.
  2. Alter the filed as auto .

  alter table YourTable column [auto] counter(1,1)

You can use the statement with Execute method of ADO connection in VB6  as follows

 adodbconnection.Execute “alter table YourTable  alter column [auto] counter(1,1)”

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.