[RESOLVED] Help me with this connection

VB Code:

Dim db As ADODB.Connection
Dim rs As ADODB.Recordset
Dim dteFormat As StdDataFormat
Dim strSQL As String
 
Set db = New ADODB.Connection
 
'A hierarchical recordset requires the MSDataShape provider. 
[B]db.Open "provider=msdatashape;data provider=sqloledb;data source=handel;" _
      & "initial catalog=northwind;integrated security=sspi"[/B]
Set rs = New ADODB.Recordset
 
strSQL = "Shape {Select CustomerId, CompanyName From Customers} " _
    & "Append ({Select CustomerId, OrderId, OrderDate From Orders} As Orders " _
    & "Relate CustomerId To CustomerId)"
 
rs.Open strSQL, db, adOpenStatic, adLockReadOnly
Set rs.ActiveConnection = Nothing
 
'Group header section - contains 1 unbound rptTextBox control
DataReport1.Sections("Section6").Controls(1).DataField = "CompanyName"
 
'details section - contains 2 unbound rptTextBox control
With DataReport1.Sections("Section1").Controls
    .Item(1).DataMember = "Orders" 'indicate the field is from the Child recordset
    .Item(1).DataField = "OrderId"
    
    Set dteFormat = New StdDataFormat
    dteFormat.Type = fmtCustom
    dteFormat.Format = "dd-MMM-yyyy"
 
    .Item(2).DataMember = "Orders"
    .Item(2).DataField = "OrderDate"
    Set .Item(2).DataFormat = dteFormat
End With
 
'Group Footer section - contains 1 unbound rptFunction control
'indicates the total # of orders per customer group
With DataReport1.Sections("Section7").Controls
    .Item(1).DataMember = "Orders"
    .Item(1).DataField = "OrderId"
    .Item(1).FunctionType = rptFuncRCnt
End With
 
DataReport1.Sections("Section7").ForcePageBreak = rptPageBreakAfter
 
Set DataReport1.DataSource = rs
DataReport1.Show
 
Set dteFormat = Nothing
Set rs = Nothing
db.Close
Set db = Nothing

I found this here in Reporting... forum

I want to know what is the proper way to connect a .MDB or MS Access 2000 file

The connection string to connect to an Access db would look something like this

Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Program Files\Microsoft Visual Studio\VB98\NWIND_2003.MDB;Persist Security Info=False

If you will also be using the shape query then you will need to differentiate provider and data provider like in your sample, since provider needs to be declared as msdatashape.

Quote:

---

Originally Posted by MarkT

The connection string to connect to an Access db would look something like this

Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Program Files\Microsoft Visual Studio\VB98\NWIND_2003.MDB;Persist Security Info=False

---

Hi, What i need is the dataShape Provider.

I'm not good in connection strings, I'm using some wizards then copy & paste them.
I know this is not recommended but try if you want. I'm using ADODataControl's(I'm not sure w/ the name but maybe it looks like it) Connection Builder.

I'm using this one too: "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=\somepath\mydb.mdb;User Id=admin;Password=;"

Here is a sample:
"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & app.path &"\mydb.mdb;User Id=admin;Password=;"
[Note]Your MSAccess database must be in same directory w/ your application/executable.

This work w/ Crystal Reports(Tested for several years) and Datareports(My former classmates used them and I see the connectionstring is same with mine but haven't used Datareports).

Quote:

---

Originally Posted by eSPiYa

I'm not good in connection strings, I'm using some wizards then copy & paste them.
I know this is not recommended but try if you want. I'm using ADODataControl's(I'm not sure w/ the name but maybe it looks like it) Connection Builder.

I'm using this one too: "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=\somepath\mydb.mdb;User Id=admin;Password=;"

Here is a sample:
"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & app.path &"\mydb.mdb;User Id=admin;Password=;"
[Note]Your MSAccess database must be in same directory w/ your application/executable.

This work w/ Crystal Reports(Tested for several years) and Datareports(My former classmates used them and I see the connectionstring is same with mine but haven't used Datareports).

---

Hi thanks for your answer but im looking for datashape provider :)

How about this:
"Provider=MSDataShape;Data Provider=Microsoft.Jet.OLEDB.4.0;Dbq=c:\somepath\mydb.mdb;User ID=myUsername;Password=myPassword"

I haven't tested it.