November 3, 2005 at 10:44 am
Hi all,
In the attached code, I can read a Table and print out the results of some
selected fields in the "Immediate" Window. How can I do the ADO-SQL coding to
get the same results saved as a "Report" object and then to print it out as a
Tabular Report? I am a new amateur in the ADO-SQL programming in the Access
2003 adp development. Please help and advise.
Thanks in advance,
SHC
/////////////////////////////////////////////////////
Sub showSQLDB()
Dim cnn As Connection
Dim rst As Recordset
Dim str As String
Dim cmd As ADODB.Command
Dim rs As ADODB.Recordset
Dim Msg As String
Dim CHIL0708A1 As Object
'Listing 22-7 (P.473 of Fronckowiak & Helda)
'Create a Connection object after instantiating it,
'this time to a SQL Server database.
Set cnn = New ADODB.Connection
cnn.Open "Provider=SQLOLEDB;Data Source=<myComputerName>;" & _
"Initial Catalog=adp1SQL;Integrated Security=SSPI;"
'Create recordset reference, and set its properties.
Set rs = New ADODB.Recordset
rs.CursorType = adOpenKeyset
rs.LockType = adLockOptimistic
'Open recordset, and print some test records.
rs.Open "CHIL0708A1", cnn
Set cmd = New ADODB.Command
'Specify the Query
cmd.CommandText = "SELECT * FROM CHIL0708A1"
cmd.CommandType = adCmdText
Set cmd.ActiveConnection = CurrentProject.Connection
Set rs = cmd.Execute(NumRecs)
'Loop Through and Display The Field Names
Msg = " "
For i = 0 To rs.Fields.Count - 1
Msg = Msg & "|" & rs.Fields(i).Name
Next
MsgBox Msg
'Loop Through and Display The Field Values for Each Record
Msg = " "
Debug.Print rs.Fields(0).Name; Spc(20); rs.Fields(6).Name; Spc(9);
rs.Fields(7).Name; Spc(8); rs.Fields(8).Name; Spc(3); rs.Fields(9).Name
rs.MoveFirst
Do While (Not rs.EOF)
If rs.Fields(0).Value = "676 NICH(BASEMENT)" Then
Debug.Print rs.Fields(0).Value, rs.Fields(6).Value, rs.Fields(7).Value,
rs.Fields(8).Value, rs.Fields(9).Value
End If
rs.MoveNext
Loop
MsgBox ("Connection was successful.")
'Clean up objects.
rs.Close
'rst.Close
cnn.Close
Set rs = Nothing
Set rst = Nothing
Set cnn = Nothing
End Sub
November 4, 2005 at 1:19 am
Hi Scott
An Access Project has all the reporting power you require. Either convert your ADO code to an SQL statement and use that as the recordSource for the report or create a temp sql table and write the data to the table, then use that as the recordSource.
November 4, 2005 at 8:43 am
The report rowsource would be 'SELECT * FROM CHIL0708A1'. Why would you want ADO to also build a report? It would be MUCH more difficult using ADO to create a formatted text file and print it out.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply