Bypass ODBC using ADO ?

M

Marc Hillman

I have a simple, single table, database in Access 97, but it contains
hierarchical data (i.e. there is a parent and child field for each record).
I wish to do reports on the data.

I'm trying to use data shaping (SHAPE, APPEND, etc) to create a recordset,
but I'm stymied. ODBC is banned in my environment (it abounds with
Luddites). How can I exploit datashaping without having any ODBC drivers? I
keep stumbling over the ADODB connection as I cannot supply a dns.

Is it possible to use data shaping without ODBC, or is there some other way
to select hierarchical data?
_______________________________________
Marc Hillman, Melbourne, Australia
web: http://users.bigpond.net.au/mhillman/
 
G

Graham R Seach

Marc,

You don't need ODBC; you can do it using OLEDB. Here's the relevant part of
a sample:

Dim cn As New ADODB.Connection
Dim rsOrders As New ADODB.Recordset
Dim rsDetails As New ADODB.Recordset
Dim strSQL As String

'Define the cursor type
cn.CursorLocation = adUseClient

'Specify the provider
cn.Provider = "MSDataShape"

'Open a connection (in this case to SQL Server)
cn.Open "Data Provider=SQLOLEDB;" & _
"Integrated Security=SSPI;Database=NorthwindCS"

'Create the SQL statement that will do all the work.
strSQL = "SHAPE {SELECT DISTINCT OrderID, " & _
"CustomerID, OrderDate " & _
"FROM Orders " & _
"WHERE Year(OrderDate) = 1996 " & _
"AND OrderID BETWEEN 10248 AND 10250 " & _
"ORDER BY OrderDate DESC} " & _
"APPEND ({SELECT OrderID, ProductID, UnitPrice, " & _
"Quantity, Discount " & _
"FROM [Order Details]} " & _
"RELATE OrderID TO OrderID)"

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia
 
G

Graham R Seach

Marc,

Sorry, the query was missing the subquery alias:
strSQL = "SHAPE {SELECT DISTINCT OrderID, " & _
"CustomerID, OrderDate " & _
"FROM Orders " & _
"WHERE Year(OrderDate) = 1996 " & _
"AND OrderID BETWEEN 10248 AND 10250 " & _
"ORDER BY OrderDate DESC} " & _
"APPEND ({SELECT OrderID, ProductID, UnitPrice, " & _
"Quantity, Discount " & _
"FROM [Order Details]} As Details " & _
"RELATE OrderID TO OrderID)"

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia
---------------------------

Graham R Seach said:
Marc,

You don't need ODBC; you can do it using OLEDB. Here's the relevant part
of a sample:

Dim cn As New ADODB.Connection
Dim rsOrders As New ADODB.Recordset
Dim rsDetails As New ADODB.Recordset
Dim strSQL As String

'Define the cursor type
cn.CursorLocation = adUseClient

'Specify the provider
cn.Provider = "MSDataShape"

'Open a connection (in this case to SQL Server)
cn.Open "Data Provider=SQLOLEDB;" & _
"Integrated Security=SSPI;Database=NorthwindCS"

'Create the SQL statement that will do all the work.
strSQL = "SHAPE {SELECT DISTINCT OrderID, " & _
"CustomerID, OrderDate " & _
"FROM Orders " & _
"WHERE Year(OrderDate) = 1996 " & _
"AND OrderID BETWEEN 10248 AND 10250 " & _
"ORDER BY OrderDate DESC} " & _
"APPEND ({SELECT OrderID, ProductID, UnitPrice, " & _
"Quantity, Discount " & _
"FROM [Order Details]} " & _
"RELATE OrderID TO OrderID)"

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia
---------------------------

Marc Hillman said:
I have a simple, single table, database in Access 97, but it contains
hierarchical data (i.e. there is a parent and child field for each
record). I wish to do reports on the data.

I'm trying to use data shaping (SHAPE, APPEND, etc) to create a
recordset, but I'm stymied. ODBC is banned in my environment (it abounds
with Luddites). How can I exploit datashaping without having any ODBC
drivers? I keep stumbling over the ADODB connection as I cannot supply a
dns.

Is it possible to use data shaping without ODBC, or is there some other
way to select hierarchical data?
_______________________________________
Marc Hillman, Melbourne, Australia
web: http://users.bigpond.net.au/mhillman/
Click to expand...
Click to expand...
 
M

Marc Hillman

Sorry Graham, but I don't have SQL Server or any other 'real' database. I
just have Access 97. I wish to do my query on an Access 97 database. Is
there a way?

Graham R Seach said:
Marc,

You don't need ODBC; you can do it using OLEDB. Here's the relevant part
of a sample:

Dim cn As New ADODB.Connection
Dim rsOrders As New ADODB.Recordset
Dim rsDetails As New ADODB.Recordset
Dim strSQL As String

'Define the cursor type
cn.CursorLocation = adUseClient

'Specify the provider
cn.Provider = "MSDataShape"

'Open a connection (in this case to SQL Server)
cn.Open "Data Provider=SQLOLEDB;" & _
"Integrated Security=SSPI;Database=NorthwindCS"

'Create the SQL statement that will do all the work.
strSQL = "SHAPE {SELECT DISTINCT OrderID, " & _
"CustomerID, OrderDate " & _
"FROM Orders " & _
"WHERE Year(OrderDate) = 1996 " & _
"AND OrderID BETWEEN 10248 AND 10250 " & _
"ORDER BY OrderDate DESC} " & _
"APPEND ({SELECT OrderID, ProductID, UnitPrice, " & _
"Quantity, Discount " & _
"FROM [Order Details]} " & _
"RELATE OrderID TO OrderID)"

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia
---------------------------

Marc Hillman said:
I have a simple, single table, database in Access 97, but it contains
hierarchical data (i.e. there is a parent and child field for each
record). I wish to do reports on the data.

I'm trying to use data shaping (SHAPE, APPEND, etc) to create a
recordset, but I'm stymied. ODBC is banned in my environment (it abounds
with Luddites). How can I exploit datashaping without having any ODBC
drivers? I keep stumbling over the ADODB connection as I cannot supply a
dns.

Is it possible to use data shaping without ODBC, or is there some other
way to select hierarchical data?
_______________________________________
Marc Hillman, Melbourne, Australia
web: http://users.bigpond.net.au/mhillman/
Click to expand...
Click to expand...
 
G

Graham R Seach

Marc,

Just use the Jet OLEDB data provider. Make sure to provide a path to the
database (strPath2JetDatabase).

'Open a connection to SQL Server
cn.Open "Data Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _
strPath2JetDatabase & ";User Id=admin;Password=;"

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia
---------------------------

Marc Hillman said:
Sorry Graham, but I don't have SQL Server or any other 'real' database. I
just have Access 97. I wish to do my query on an Access 97 database. Is
there a way?

Graham R Seach said:
Marc,

You don't need ODBC; you can do it using OLEDB. Here's the relevant part
of a sample:

Dim cn As New ADODB.Connection
Dim rsOrders As New ADODB.Recordset
Dim rsDetails As New ADODB.Recordset
Dim strSQL As String

'Define the cursor type
cn.CursorLocation = adUseClient

'Specify the provider
cn.Provider = "MSDataShape"

'Open a connection (in this case to SQL Server)
cn.Open "Data Provider=SQLOLEDB;" & _
"Integrated Security=SSPI;Database=NorthwindCS"

'Create the SQL statement that will do all the work.
strSQL = "SHAPE {SELECT DISTINCT OrderID, " & _
"CustomerID, OrderDate " & _
"FROM Orders " & _
"WHERE Year(OrderDate) = 1996 " & _
"AND OrderID BETWEEN 10248 AND 10250 " & _
"ORDER BY OrderDate DESC} " & _
"APPEND ({SELECT OrderID, ProductID, UnitPrice, " & _
"Quantity, Discount " & _
"FROM [Order Details]} " & _
"RELATE OrderID TO OrderID)"

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia
---------------------------

Marc Hillman said:
I have a simple, single table, database in Access 97, but it contains
hierarchical data (i.e. there is a parent and child field for each
record). I wish to do reports on the data.

I'm trying to use data shaping (SHAPE, APPEND, etc) to create a
recordset, but I'm stymied. ODBC is banned in my environment (it abounds
with Luddites). How can I exploit datashaping without having any ODBC
drivers? I keep stumbling over the ADODB connection as I cannot supply a
dns.

Is it possible to use data shaping without ODBC, or is there some other
way to select hierarchical data?
_______________________________________
Marc Hillman, Melbourne, Australia
web: http://users.bigpond.net.au/mhillman/
Click to expand...
Click to expand...
Click to expand...
 
G

Graham R Seach

Oops, cut-n-paste!

'Open a connection to *Jet*
cn.Open "Data Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _
strPath2JetDatabase & ";User Id=admin;Password=;"

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia
---------------------------

Graham R Seach said:
Marc,

Just use the Jet OLEDB data provider. Make sure to provide a path to the
database (strPath2JetDatabase).

'Open a connection to SQL Server
cn.Open "Data Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _
strPath2JetDatabase & ";User Id=admin;Password=;"

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia
---------------------------

Marc Hillman said:
Sorry Graham, but I don't have SQL Server or any other 'real' database. I
just have Access 97. I wish to do my query on an Access 97 database. Is
there a way?

Graham R Seach said:
Marc,

You don't need ODBC; you can do it using OLEDB. Here's the relevant part
of a sample:

Dim cn As New ADODB.Connection
Dim rsOrders As New ADODB.Recordset
Dim rsDetails As New ADODB.Recordset
Dim strSQL As String

'Define the cursor type
cn.CursorLocation = adUseClient

'Specify the provider
cn.Provider = "MSDataShape"

'Open a connection (in this case to SQL Server)
cn.Open "Data Provider=SQLOLEDB;" & _
"Integrated Security=SSPI;Database=NorthwindCS"

'Create the SQL statement that will do all the work.
strSQL = "SHAPE {SELECT DISTINCT OrderID, " & _
"CustomerID, OrderDate " & _
"FROM Orders " & _
"WHERE Year(OrderDate) = 1996 " & _
"AND OrderID BETWEEN 10248 AND 10250 " & _
"ORDER BY OrderDate DESC} " & _
"APPEND ({SELECT OrderID, ProductID, UnitPrice, " & _
"Quantity, Discount " & _
"FROM [Order Details]} " & _
"RELATE OrderID TO OrderID)"

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia
---------------------------

I have a simple, single table, database in Access 97, but it contains
hierarchical data (i.e. there is a parent and child field for each
record). I wish to do reports on the data.

I'm trying to use data shaping (SHAPE, APPEND, etc) to create a
recordset, but I'm stymied. ODBC is banned in my environment (it
abounds with Luddites). How can I exploit datashaping without having
any ODBC drivers? I keep stumbling over the ADODB connection as I
cannot supply a dns.

Is it possible to use data shaping without ODBC, or is there some other
way to select hierarchical data?
_______________________________________
Marc Hillman, Melbourne, Australia
web: http://users.bigpond.net.au/mhillman/
Click to expand...
Click to expand...
Click to expand...
 
Top