Connection string issue with MS access

[1981.madhu@gmail.com]

Hi ,
I have created a database in MSaccess(.mdb).while i connecting in the qtp .Its giving error

My issue is i want to conenct to db and retrive one table and its value in the external excel sheet or datatable

my code :

Code:

set a=createobject('Odbc.connection")
a.open provider="my connection string"
set b =createobject('Odbc.recordset")
b.open "select * from employees",a

while runing its stopping at second line .i think my connection string is wrong .hw can i get the correct connection string for .mdb file.

pls help

[Jackomcnabb]

go to ConnectionStrings.com

[pavansri]

you should create a object for "adodb.connection" not "Odbc.connection"
anyways following code should solve your problem..

Code:

Set conn = CreateObject("adodb.connection")
Set rec = CreateObject("adodb.recordset")

conn.Provider = "microsoft.jet.oledb.4.0"
conn.Open "specify path of your access database file here"

rec.Open "specify your query here", conn

[1981.madhu@gmail.com]

Hi pavan

I followed this code

Code:

Set con = CreateObject("adodb.connection")
Set rs = CreateObject("adodb.recordset")

con.open "Provider=my query string"
rs.open "sql query ",con
i=0
do until rs<>eof
dim arr
arr(i)=rs("Columnname").value

i=i+1

rs.movenext

loop

.

my scenario is i want to connect database and from one table 's column 's value fetch and save in excel sheet or datatable.

ex.form employee table i want to fetch First name column's values in excel r datatable.

pls help

[1981.madhu@gmail.com]

pls help on this issue


Thanks in advance

[sreekanth chilam]

Hi Madhu,

Check out the below example & implement accordingly.

Note: Create a Datasource("flight32.dsn") in C:\Program Files\Common Files\ODBC\Data Sources( which is assigned to FILEDSN variable in below code)

Code:

Dim db
Set db=CreateObject("ADODB.Connection")
db.connectionString="DBQ=C:\PROGRAM FILES\HP\QuickTest Professional\samples\flight\app\flight32.mdb;DefaultDir=C:\PROGRAM FILES\HP\QuickTest Professional\samples\flight\app;Driver={Microsoft Access Driver (*.mdb)};DriverId=281;FIL=MS Access;FILEDSN=C:\Program Files\Common Files\ODBC\Data Sources\flight32.dsn;MaxBufferSize=2048;MaxScanRows=8;PageTimeout=5;SafeTransactions=0;Threads=3;UID=admin;UserCommitSync=Yes;"
db.Open
Set RecordSet=db.Execute("Select * from Orders")
i=1
While (NOT recordset.EOF)
      Datatable.SetCurrentRow(i)
       Datatable("CustomerName",dtGlobalSheet)=Recordset.Fields("Customer_Name")
        msgbox Datatable("CustomerName",dtGlobalSheet)
       Recordset.MoveNext
       i=i+1
Wend
db.Close
Set db=Nothing

Once test run is finished,check in RunTime Datatable of Test Results, entire Backend Table's data under "Customer_Name" column will imported to QTP Datatable's Column "CustomerName"

Hope this would solve your issue.