Connection string issue with MS access

Connection string issue with MS access - Printable Version

+- Micro Focus QTP (UFT) Forums (https://www.learnqtp.com/forums)
+-- Forum: Micro Focus UFT (earlier known as QTP) (https://www.learnqtp.com/forums/Forum-Micro-Focus-UFT-earlier-known-as-QTP)
+--- Forum: UFT / QTP Beginners (https://www.learnqtp.com/forums/Forum-UFT-QTP-Beginners)
+--- Thread: Connection string issue with MS access (/Thread-Connection-string-issue-with-MS-access)

Connection string issue with MS access - 1981.madhu@gmail.com - 11-19-2009

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

RE: Connection string issue with MS access - Jackomcnabb - 11-19-2009

go to ConnectionStrings.com

RE: Connection string issue with MS access - pavansri - 11-20-2009

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

RE: Connection string issue with MS access - 1981.madhu@gmail.com - 11-20-2009

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

RE: Connection string issue with MS access - 1981.madhu@gmail.com - 11-20-2009

pls help on this issue

Thanks in advance

RE: Connection string issue with MS access - sreekanth chilam - 11-21-2009

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. Smile