How can i fetch data from ms access database

esheL - Dec 10, 2009 at 10:38 PM
 Basheer - Sep 8, 2010 at 02:58 AM
how can I fetch data from an ms access database and show it in my form using a combo box?!

just like search engines...

can you hand me some useful codes?

3 responses

Given you've already connected to your database, we'll make this quick.

<form method="post" action="?act=search">
'adoCon will be the connection reference to your database object
Dim values, strSQL
Set values = Server.CreateObject("ADODB.RecordSet")
strSQL = "Select * From values"
values.Open strSQL, adoDB
items_array = values("SearchItems")
'Count the values using the separater
items_total = UBound(Split(items_array, "|")) + 1
'Split items into array
options = Split(items_array, "|")
Response.Write("<select name='search'>")
For I = 1 to items_total
Response.Write("<option value='" & options(i) & "'>" & options(i) & "</option>")
closeup22 Posts 8923 Registration date Friday May 15, 2009 Status Member Last seen October 7, 2010 2,099
Dec 11, 2009 at 07:38 AM
hi there,

please give more information

This is VB6 code to DataBase(mdb) and also you can save that data in New Ms Excel Sheet.

Private Sub CmdTransfertoExcel_Click()

' Open my MS Access Database
Set Con = New ADODB.Connection
Set rscat = New ADODB.Recordset
Con.Open "Provider=Microsoft.Jet.Oledb.4.0;Data Source=" & App.Path & "\AccPro.Mdb"

strSql1 = "select StockLotNo,StockDate, StockStoneName, StockSize, StockShape, StockPcs, StockCts, StockCost, StockSubAmount from TblNStock" & intStyleID
rscat.Open strSql1, Con, adOpenKeyset, adLockOptimistic

'Create a new workbook in Excel
Dim oExcel As Object
Dim oBook As Object
Dim oSheet As Object
Set oExcel = CreateObject("Excel.Application")
Set oBook = oExcel.Workbooks.Add
Set oSheet = oBook.Worksheets(1)

oSheet.Range("A1").Value = "LOT NO."
oSheet.Range("B1").Value = "DATE"
oSheet.Range("C1").Value = "STONE NAME"
oSheet.Range("D1").Value = "SIZE"
oSheet.Range("E1").Value = "SHAPE"
oSheet.Range("F1").Value = "PCS"
oSheet.Range("G1").Value = "CTS"
oSheet.Range("H1").Value = "@ COST"
oSheet.Range("I1").Value = "TOTAL"
oSheet.Range("A1:I1").Font.Bold = True

'Transfer the data to Excel
oSheet.Range("A2").CopyFromRecordset rscat
Dim SaveFileOfStock

'Save the Workbook and Quit Excel
SaveFileOfStock = Trim("Stock " + Format$(Now, "d mmm yyyy") + " " + Format$(Now, "hh mm ss"))
oBook.SaveAs "C:\" & SaveFileOfStock & ".xls"

'Close the connection
MsgBox ("Excel File Created Successfully. Path is C:\Stock.xls with Date and Time. Thank You.")

End Sub

Hope this may help you.