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.