Creating a Recordset

[DAvid Hunt]

I'm trying to make a recordset so that I can run an SQL
statement on said recordset. Confused with the syntax to
use...

'********** code below ************

Dim rcdst As New ADODB.Recordset
Dim conn As New ADODB.Connection
Dim SQL as String

Set SQL = "Select * from [Data], [Charges] _
where is NULL([Data].[Grand Total])"


Set conn = New ADODB.Connection
Set rcdst = New ADODB.Recordset

Set conn = CurrentProject.Connection
rcdst.Open SQL, conn, adOpenStatic, adLockOptimistic,
adCmdTable

************

This doeesn't look at all right?

 

[Tim Ferguson]

I Am No ADO Guru But...

Set SQL = "Select * from [Data], [Charges] _
where is NULL([Data].[Grand Total])"

You don't need the Set because this is a straight assignment. You can't use
the line continuation character inside the string. The IS NULL phrase
doesn't quite look like that:

strSQL = "SELECT Charges FROM Data " & _
"WHERE [Grand Total] IS NULL"

PS Using spaces in field names is a Bad Idea.

Set conn = CurrentProject.Connection
rcdst.Open SQL, conn, adOpenStatic, adLockOptimistic,
adCmdTable

The help file says:

recordset.Open Source, ActiveConnection, CursorType, LockType, Options

so this means something like

rcdst.Open strSQL, conn, adOpenStatic, adLockOptimistic, adCmdText

Note that it's not adCmdTable because you are passing a SQL string rather
than the name of a table.


Hope that helps


Tim F

 

[dchendrickson]

I'll add my 2cw...

The recordset.open syntax is close. When using SQL as the
source, you can either leave the options argument blank
(which defaults to adCmdUnknown) or you can use adCmdText
(tells the provider that the Source argument is a textual
file, such as a SQL statement). So you might have
somthing like:

rst.open SQL,conn,adOpenStatic,adLockOptimistic,adCmdText
or
rst.open SQL,conn,adOpenStatic,adLockOptimistic

I am not sure how familiar you are with SQL, but the
general format of your statement should be:

SELECT field names or * for all field
FROM table/query name
WHERE IsNull([Data].[Grand Total])

SQL = "SELECT * FROM [Data] " & _
"WHERE IsNull([Data].[Grand Total]);"

Not necessary, but I might even try:

SQL = "SELECT d.* FROM Data AS d " & _
"WHERE IsNull(d.[Grand Total]);"

Not sure in your original posting if [Charges] is a table
name or a field name within the [Data] table. If
[Charges] is a field name within [Data] and it is the
only field you want to return then:

SQL = "SELECT d.[Charges] FROM [Data] AS d " & _
"WHERE IsNull(d.[Grand Total]);"

Hope this gives you a little something extra to chew on.

-dc

-----Original Message-----

I Am No ADO Guru But...

Set SQL = "Select * from [Data], [Charges] _
where is NULL([Data].[Grand Total])"

You don't need the Set because this is a straight assignment. You can't use
the line continuation character inside the string. The IS NULL phrase
doesn't quite look like that:

strSQL = "SELECT Charges FROM Data " & _
"WHERE [Grand Total] IS NULL"

PS Using spaces in field names is a Bad Idea.

Set conn = CurrentProject.Connection
rcdst.Open SQL, conn, adOpenStatic, adLockOptimistic,
adCmdTable

The help file says:

recordset.Open Source, ActiveConnection, CursorType, LockType, Options

so this means something like

rcdst.Open strSQL, conn, adOpenStatic, adLockOptimistic, adCmdText

Note that it's not adCmdTable because you are passing a SQL string rather
than the name of a table.


Hope that helps


Tim F

.