Results 1 to 4 of 4
  1. #1
    4 Star Lounger
    Join Date
    Mar 2005
    Location
    Leicestershire, United Kingdom
    Posts
    506
    Thanks
    0
    Thanked 0 Times in 0 Posts

    ADODB to SQL Server (2000)

    Hi,

    We are looking to change our backend to SQL Server 2005 and are currently testing it.

    On one of our forms, we have the following code attached to a Command Button:

    Private Sub Command696_Click()
    Dim cnn As ADODB.Connection
    Dim rst As New ADODB.Recordset
    Dim varsalesid As Variant
    Dim strlogon As String

    'Find logon information
    strlogon = Environ("Username")
    varsalesid = DLookup("[SALESID]", "tblsalesman", "Winlogon=" & Chr(34) & strlogon & Chr(34))
    officeresid = DLookup("[valuerbased]", "tblValuer", "ValuerID=" & Me!ValuerID)



    If DCount("*", "tblProperty", "propnumr=" & Chr(34) & Me!VHouseNo & Chr(34) & " and streetname=" & Chr(34) & Me!VStreetName & Chr(34)) = 0 Then


    Set cnn = CurrentProject.Connection
    rst.Open "tblProperty", cnn, adOpenKeyset, adLockOptimistic, adCmdTableDirect
    rst.AddNew
    rst!PROPNUMR = Me!VHouseNo
    rst!STREETNAME = Me!VStreetName
    rst!ADDRESS2 = Me!VStreet2
    rst!AREA = Me!VArea
    rst!CITY = VCity
    rst!POSTCODE = Me!VPostcode
    rst!VENDORTEL1 = Me!HomeTel
    rst!VENDORTEL2 = Me!WorkTel
    rst!VMobile = Me!MobileTel
    rst!CHuseNum = Me!CHouseNo
    rst!CAddress1 = Me!CStreetName
    rst!CAddress2 = Me!CStreet2
    rst!CAddress3 = Me!CArea
    rst!CTown = Me!CCity
    rst!CPostcode = Me!CPostcode
    rst!OfficeID = officeresid
    rst!VTitle1 = Me!Title1
    rst!VTitle2 = Me!Title2
    rst!VFName1 = Me!FName1
    rst!VFname2 = Me!FName2
    rst!VSurname1 = Me!Surname1
    rst!VSurname2 = Me!Surname2
    rst!Email = Me!EmailAddress
    rst!ENTRYDATE = Date
    rst!ValuerID = Me.ValuerID
    rst!OfficeID = officeresid
    rst!Status = "For Sale"
    rst.Update
    rst.Save
    rst.Close

    Set rst = Nothing
    Set cnn = Nothing

    I understand that with SQL Server that the server must be called for this action to work correctly, because at present it does not 'update' or 'save' and causes an odbc error.

    Any suggestions / pointers?

    Thanks in advance.

    Luke
    Best Regards,

    Luke

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: ADODB to SQL Server (2000)

    The Save method of an ADODB recordset is not intended to save the current record, but to save the entire recordset to an external file (an ADTG or XML file), so you shouldn't use that here.

  3. #3
    4 Star Lounger
    Join Date
    Mar 2005
    Location
    Leicestershire, United Kingdom
    Posts
    506
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: ADODB to SQL Server (2000)

    So, how would the newly created record get saved?
    Best Regards,

    Luke

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: ADODB to SQL Server (2000)

    The line

    rst.Update

    should save the record.

Posting Permissions

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