Results 1 to 3 of 3
  1. #1
    Join Date
    Jul 2002
    Dunedin NZ

    Angry Unanswered: ADO update with spaces in the field name

    Hi folks

    I'm trying to convert an access DB to using a SQL Server backend.
    The table currently have lots of field names with spaces in them eg [field name]

    The ADO Update method fails if I use one of these field names ([new Val] in the example below) however it works fine if the field name contain no spaces.

    See code below.

    Any help would be greatly appreciated

    Private Sub Command4_Click()
    Dim strCnn As String
    Dim stSql As String
    Dim rs As ADODB.Recordset
    strCnn = "ODBC;DATABASE=dbName;UID=sa;PWD=;" & _

    Set rs = New ADODB.Recordset
    rs.CursorType = adOpenKeyset
    rs.LockType = adLockOptimistic
    rs.Open "[jgg]", strCnn, , , adCmdTable

    rs.Find "Number = 1"

    rs!Name = txtName
    rs![new Val] = txtVal
    jgg.[new Val] = txtVal

    Set rs = Nothing
    Set con = Nothing

    End Sub

    Please note this code is not very functional, but it demonstrates the problem.

    Thanks for any thoughts you may have

  2. #2
    Join Date
    Apr 2002
    Mhh, try to halt the code in debug mode (maybe at line: rs.Find "Number = 1") and monitor the rs object variable. Open up the fields collection and have a look at the individual field names.
    Or try if rs.Fields("Field Name") helps.

    Hope that helps a bit, cheers


  3. #3
    Join Date
    Jul 2002
    Dunedin NZ
    Thanks Michael
    I have tried rs.Fields("Field Name") with no luck.
    Thought it was a good idea to try to look through the fields collection at the individual names.
    So I did except I'm at home now and this is where the plot thickens.

    The code works as you would expect (after removing the jgg.[new Val] = txtVal, a line I added later and forget to remove in the post).

    At home SQL Server is just running off HOMEPC but why does it work here and why not on our server???

    Hope somebody has some idea cause I don't


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts