[RESOLVED] Help me with this connection

[o0yuna0o]

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

[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

[leinad31]

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.

[o0yuna0o]

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.

[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).

[o0yuna0o]

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

[eSPiYa]

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.