Results 1 to 9 of 9
  1. #1
    Snowball's Avatar
    Snowball is offline Novice
    Windows 7 64bit Access 2003
    Join Date
    Jan 2012
    Location
    Athens - Greece
    Posts
    8

    How can i change VBA code in a form in order to target SQL Server table?

    Hi, everyone, this is my first post, it's nice that i have found this forum!



    I am totaly new to Access and VBA programming so i will need your help...

    We have an Access 2003 database (i am not the creator) that we upsized in SQL Server 2005...

    What i am trying to do is rewrite the VBA code in some forms in order to target the linked SQL Server table...

    The original code is this:

    Code:
    Private Sub SNO_AfterUpdate()
    Dim db As Database, R As Recordset
    Dim F As Field
    Dim CDFIB As TableDef
    Dim ORDD As Forms
    Dim strCriteria As String
    
    Set db = OpenDatabase("C:\Users\dapostolop\Desktop\old2\old\Papazoglou_OLD.mdb")
    
    strCriteria = "[SNO] = " & Forms!ORDD!SNO & " And [CDCODE] = " & Forms!ORDD!CDCODE & ""
    
    Set R = db.OpenRecordset("CDFIB", dbOpenDynaset)
    
    With R
    38          .FindFirst strCriteria
         If .NoMatch Then
             Forms!ORDD!CDFIB.Value = Null
             MsgBox "  NO FIBNO..!!   "
         Else
             Set F = R.Fields!CDFIBNO
             Forms!ORDD!CDFIB = F.Value
         End If
    End With
    Set db = Nothing
    End Sub
    I have created a DSN ODBC connection using SQL Server driver in order to connect to the SQL Server database (i have used it for the upsizing) which is in another machine...

    Any help will be appreciated...

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Do you have links to the tables? If so, don't see need for the db variable.

    Are table names the same?

    Try:
    Set R = CurrentDb.OpenRecordset("CDFIB", dbOpenDynaset)

    If tables not linked, then correct the path string.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    Snowball's Avatar
    Snowball is offline Novice
    Windows 7 64bit Access 2003
    Join Date
    Jan 2012
    Location
    Athens - Greece
    Posts
    8
    Quote Originally Posted by June7 View Post
    Do you have links to the tables? If so, don't see need for the db variable.

    Are table names the same?

    Try:
    Set R = CurrentDb.OpenRecordset("CDFIB", dbOpenDynaset)

    If tables not linked, then correct the path string.
    I have upsized Access in SQL Server (forms and interface on Access, storage on SQL Server)...

    The tables are linked and have the same names with Access tables (Access tables are aytomaticaly renamed to "tablename_local")....

    I tried what you suggested but there is a great delay, the form freezes...

    I also used dbSeeChanges but i had no luck...

    Yesterday i was experimenting to connect to the SQL Server database through DSN connection and at least i managed to connect to the database...

  4. #4
    Snowball's Avatar
    Snowball is offline Novice
    Windows 7 64bit Access 2003
    Join Date
    Jan 2012
    Location
    Athens - Greece
    Posts
    8
    Well i 'm making some progress here, i believe i am close to the end of my quest...

    As i wrote in my previous post, i was experimenting with ADODB connection and recordsets and i managed to write the following code that contains no errors but loops from here to eternity...

    Code:
    Private Sub SNO_AfterUpdate()
    
    Dim F As ADODB.Field
    Dim ORDD As Forms
     
    Dim cnn As ADODB.Connection
    Set cnn = New ADODB.Connection
    Dim CnnStr As String
    Dim rs1 As New ADODB.Recordset
     
    CnnStr = "DSN=PAPAZOGLU;UID=admin;PWD=password;"
     
    cnn.ConnectionString = CnnStr
    cnn.Open
     
    rs1.ActiveConnection = cnn
    rs1.CursorType = adOpenForwardOnly
    rs1.Open "SELECT SNO, CDCODE, CDFIBNO FROM CDFIB"
     
     
    rs1.MoveFirst
    Do Until rs1.EOF
        If (rs1!SNO = Forms!ORDD!SNO And rs1!CDCODE = Forms!ORDD!CDCODE) Then
            Set F = rs1!CDFIBNO
            Forms!ORDD!CDFIB = F.Value
        Else
            Forms!ORDD!CDFIB.Value = Null
            MsgBox "  NO FIBNO!!!  "
        End If
        rs1.MoveNext
    Loop
    
    rs1.Close
    Set rs1 = Nothing
     
    cnn.Close
    Set cnn = Nothing
     
    End Sub
    I believe the logic behind the code is pretty clear, what am i doing wrong???

  5. #5
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Have you step debugged?

    I am surprised if the recordset opens. It is my understanding of Open method that semi-colon and arguments required:
    rs1.Open "SELECT SNO, CDCODE, CDFIBNO FROM CDFIB;", cnn, [cursor type], [lock type]

    Also, the F variable is not needed.
    Me.CDFIB = rs1!CDFIBNO

    Use dot instead of ! when referencing Access objects and controls. The dot will provoke intellisense popups.

    Value is default property, no need to type it.

    Otherwise, the logic does look good.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  6. #6
    Snowball's Avatar
    Snowball is offline Novice
    Windows 7 64bit Access 2003
    Join Date
    Jan 2012
    Location
    Athens - Greece
    Posts
    8
    I am close to the end...

    I managed to do what i want but now i have to work on performance...

    Code:
    Private Sub SNO_AfterUpdate()
    
    Dim F As ADODB.Field
    Dim ORDD As Forms
    Dim found As Integer
    found = 0
     
    Dim cnn As ADODB.Connection
    Set cnn = New ADODB.Connection
    Dim CnnStr As String
    Dim rs1 As New ADODB.Recordset
     
    CnnStr = "DSN=PAPAZOGLU;UID=admin;PWD=password;"
     
    cnn.ConnectionString = CnnStr
    cnn.Open
     
    rs1.ActiveConnection = cnn
    rs1.CursorType = adOpenForwardOnly
    rs1.Open "SELECT SNO, CDCODE, CDFIBNO FROM CDFIB"
    
    rs1.MoveFirst
    Do Until rs1.EOF
        If (rs1!SNO = Forms!ORDD!SNO And rs1!CDCODE = Forms!ORDD!CDCODE) Then
            found = 1
            Exit Do
        End If
        rs1.MoveNext
    Loop
    If found = 1 Then
        Set F = rs1!CDFIBNO
        Forms!ORDD!CDFIB = F.Value
    Else
        Forms!ORDD!CDFIB.Value = Null
        MsgBox "  NO FIBNO!!!  "
    End If
     
    rs1.Close
    Set rs1 = Nothing
     
    cnn.Close
    Set cnn = Nothing
     
    End Sub

  7. #7
    Snowball's Avatar
    Snowball is offline Novice
    Windows 7 64bit Access 2003
    Join Date
    Jan 2012
    Location
    Athens - Greece
    Posts
    8
    Problem solved, here is the code, working just fine...

    Code:
    Private Sub SNO_AfterUpdate()
    
    Dim F As ADODB.Field
    Dim ORDD As Forms
    Dim mySQL As String
    Dim fSNO As Variant
    Dim fCDCODE As Variant
     
    fSNO = Forms!ORDD!SNO
    fCDCODE = Forms!ORDD!CDCODE
    
    Dim cnn As ADODB.Connection
    Set cnn = New ADODB.Connection
    Dim CnnStr As String
    Dim rs1 As New ADODB.Recordset
     
    CnnStr = "DSN=PAPAZOGLU;UID=admin;PWD=password;"
     
    cnn.ConnectionString = CnnStr
    cnn.Open
    
    mySQL = "SELECT SNO, CDCODE, CDFIBNO FROM CDFIB WHERE SNO=" & fSNO & "AND CDCODE=" & fCDCODE
     
    rs1.ActiveConnection = cnn
    rs1.CursorType = adOpenForwardOnly
    rs1.Open (mySQL)
    
    If rs1.BOF And rs1.EOF Then
        Forms!ORDD!CDFIB.Value = Null
        MsgBox "  NO FIBNO!!!  "
    Else
        If (rs1!SNO = Forms!ORDD!SNO And rs1!CDCODE = Forms!ORDD!CDCODE) Then
            Set F = rs1!CDFIBNO
            Forms!ORDD!CDFIB = F.Value
        Else
            Forms!ORDD!CDFIB.Value = Null
            MsgBox "  NO FIBNO!!!  "
        End If
    End If
        
    rs1.Close
    Set rs1 = Nothing
     
    cnn.Close
    Set cnn = Nothing
     
    End Sub

  8. #8
    ssanfu is offline Master of Nothing
    Windows 2K Access 2000
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Congratulations on getting it to work. But it does seem like you took the long way around.

    In our application, we are using SS2005 on the server, I also installed SSE2005 on my laptop for development. On each client computer that needed to access the database, we created an ODBC connection using SQL Server driver with Windows authentication.
    Then we linked the A2K FE to SQL Server. SQL Server prefixes dbo_ to each of the tables that was upsized; when we relinked the FE to SS, we used the original table names (without the dbo).

    All this is using DAO, not ADO. In our case, DAO seems to be faster, rather than opening a connection to SS through ADO.

    I log on to my computer, open the A2K FE and start to work. No problems. We are thinking about converting to gigabit network cards to increase transfer speed - after we justify the money.

    Just my 2 cents......

  9. #9
    Snowball's Avatar
    Snowball is offline Novice
    Windows 7 64bit Access 2003
    Join Date
    Jan 2012
    Location
    Athens - Greece
    Posts
    8
    Quote Originally Posted by ssanfu View Post
    Congratulations on getting it to work. But it does seem like you took the long way around.

    In our application, we are using SS2005 on the server, I also installed SSE2005 on my laptop for development. On each client computer that needed to access the database, we created an ODBC connection using SQL Server driver with Windows authentication.
    Then we linked the A2K FE to SQL Server. SQL Server prefixes dbo_ to each of the tables that was upsized; when we relinked the FE to SS, we used the original table names (without the dbo).

    All this is using DAO, not ADO. In our case, DAO seems to be faster, rather than opening a connection to SS through ADO.

    I log on to my computer, open the A2K FE and start to work. No problems. We are thinking about converting to gigabit network cards to increase transfer speed - after we justify the money.

    Just my 2 cents......
    Good morning, ssanfu!

    The table names i am using are exactly the same as before the upsizing...

    I used DAO at first but nothing seemed to work right...

    Cheers!

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Query to flag daily change in order status
    By Relyuchs in forum Queries
    Replies: 1
    Last Post: 01-21-2011, 02:53 PM
  2. Replies: 1
    Last Post: 11-07-2010, 11:04 AM
  3. Records change order
    By accessbeginner in forum Access
    Replies: 1
    Last Post: 03-18-2010, 06:38 PM
  4. Replies: 0
    Last Post: 03-09-2009, 12:20 PM
  5. Replies: 1
    Last Post: 08-10-2008, 01:09 AM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums