Dynamic SQL and ADO Recordset when table field names have spaces

[John F]

I need to work with a table that was has 2 field names each with a space:
Proposal Name and Proposal Number.

I am building a SQL string, then opening an ADO Recordset and finally using
the Fields property of the recordset.

How should I handle these field names, both when defining the SQL string and
using the Fields property?

Thanks,

John





Dim strSQL As String
Dim MsgStr As String
Dim TitleStr As String

Dim rsProp As New ADODB.Recordset

Dim strProposalName, strProposalNumber As String


strSQL = "SELECT ProposalKey, Proposal Name, Proposal Number FROM
tblProposals " _
& "WHERE ProposalKey = " & Me.txtProposalID

rsProp.Open strSQL, CurrentProject.Connection, adOpenForwardOnly,
adLockReadOnly

If rsProp.EOF Then
MsgBox "This Proposal ID does not exist in the Proposal Status Data
Base.", _
vbCritical, "Import Error"
rsProp.Close
Set rsProp = Nothing
Exit Sub
End If

strProposalName = rsProp.Fields("Proposal Name")
strProposalNumber = rsProp.Fields("Proposal Number")

rsProp.Close
Set rsProp = Nothing

MsgStr = "Proposal Name: " & strProposalName & " Proposal Number: " _
& strProposalNumber & " Continue with the import?"
TitleStr = "Confirm Proposal Import"
If MsgBox(MsgStr, vbYesNo, TitleStr) = vbNo Then
Exit Sub
End If

 

[John F]

Forgot to mention Access 2003

Thanks,

John

 

[Dirk Goldgar]

I need to work with a table that was has 2 field names each with a space:
Proposal Name and Proposal Number.

I am building a SQL string, then opening an ADO Recordset and finally
using
the Fields property of the recordset.

How should I handle these field names, both when defining the SQL string
and
using the Fields property?

Thanks,

John





Dim strSQL As String
Dim MsgStr As String
Dim TitleStr As String

Dim rsProp As New ADODB.Recordset

Dim strProposalName, strProposalNumber As String


strSQL = "SELECT ProposalKey, Proposal Name, Proposal Number FROM
tblProposals " _
& "WHERE ProposalKey = " & Me.txtProposalID

rsProp.Open strSQL, CurrentProject.Connection, adOpenForwardOnly,
adLockReadOnly

If rsProp.EOF Then
MsgBox "This Proposal ID does not exist in the Proposal Status Data
Base.", _
vbCritical, "Import Error"
rsProp.Close
Set rsProp = Nothing
Exit Sub
End If

strProposalName = rsProp.Fields("Proposal Name")
strProposalNumber = rsProp.Fields("Proposal Number")

rsProp.Close
Set rsProp = Nothing

MsgStr = "Proposal Name: " & strProposalName & " Proposal Number: "
_
& strProposalNumber & " Continue with the import?"
TitleStr = "Confirm Proposal Import"
If MsgBox(MsgStr, vbYesNo, TitleStr) = vbNo Then
Exit Sub
End If

In building the SQL string, wrap the improper field names in square
brackets:

strSQL = _
"SELECT ProposalKey, [Proposal Name], [Proposal Number] " & _
"FROM tblProposals " & _
"WHERE ProposalKey = " & Me.txtProposalID

The recordset field references are fine as they are:

 

[John F]

Dirk,

Thanks very much.

John