Example of ADODB.Connection and ADODB.Recordset in VBscript

    Set MyConn = CreateObject("ADODB.Connection")
    MdbFilePath = "C:\Users\ladn\Desktop\Neeraj.mdb"
    MyConn.Open "Driver={Microsoft Access Driver (*.mdb)}; DBQ=" & MdbFilePath & ";"

 
    SQL_query = "SELECT * from Student"
 
     set rs=CreateObject("ADODB.recordset")
     rs.Open SQL_query, MyConn
 
    count = 0
    WHILE NOT RS.EOF
count = count + 1
name = rs("name")
add = rs("address")
msgbox name & add
       rs.MoveNext
    WEND
msgbox count
    rs.Close
    set rs = nothing
    MyConn.close
    set MyConn = nothing

ODBC Connection for MS Access

• Click on Start button
• Select Control Panal
• Click on Administrator Tools
• Click on Data Sources (ODBC)
• ODBC Data Source Administrator window will open as below screenshot

• To Add MS Access User Data Source, Click on Add button
• Create New Data Source window will open

• Select “Diver do Microsoft Access (*.mdb)”  from Create New Data Source window and click on finish button.
• ODBC Microsoft Access Setup Window will open

• Write Data Source Name and Description if need,
• Click on Select button  and select mdb file from the location (see below screenshot)

• Click on OK button after selecting mdb file

• Database will display under ODBC Microsoft Access Setup window and click on OK button

• MS Access User DSN Will display MS Access Driver name.

Retrive the column data from database in vbscript

Function retrive_Column_Data()
    Dim fieldname1 As String
    Dim cntAs Integer
    Dim fieldsArr()
   
    cnt= 0
    Set rs = CreateObject("adodb.recordset")
  
    sqlquery = "enter here Select query"
    Set rs = CreateObject("adodb.recordset")
    rs.Open sqlquery, con
    rs.movefirst
   
    While Not rs.EOF
       
        cnt= cnt+ 1       
        rs.movenext
       
    Wend
   
    rs.movefirst
    ReDim testdatadetailsarr(cnt- 1)
   
    While Not rs.EOF
       
        For I = 0 To cnt- 1
            fieldname1 = rs("dbcolumnName_1")
            fieldname2=  rs("dbcolumnName_2")
            fieldsArr(I) = fieldname1 & "," & fieldname2
            rs.movenext
           
        Next
       
    Wend
   
    retrive_Column_Data= fieldsArr
    Set rs = Nothing
    Erase fieldsArr

End Function

Function to find row count from database in vbscript

Function rowCount()
   
    Dim cnt As Integer
   
    cnt = 0
   
    sqlquery = "enter here Select query"
    Set rs = CreateObject("adodb.recordset")
    rs.Open sqlquery, con
    If rs.BOF = False And rs.EOF = False Then
   
        rs.movefirst
   
        While Not rs.EOF
       
            cnt = cnt + 1
            rs.movenext
           
        Wend
       
    rowCount = cnt
    End If
    Set rs = Nothing
   
End Function

Adodb connection in vbscript

Function OpenDBConnect()
   
    Set con = CreateObject("adodb.connection")
    con .ConnectionString = "Enter here Connection string"
    con .Open

End Function