Results 1 to 10 of 10
  1. #1
    LauraSWilde is offline Novice
    Windows 10 Office 365
    Join Date
    Feb 2022
    Posts
    6

    Question Module to Update Access DB from SQL table

    Hey,

    Apologies in advance; I am new to using Access. I've written Excel macros etc but am now learning my way through Access.



    Anyhow, I wrote a module that when executed updates and SQL table from an access database. It deleted the data in the SQL table and then uploads the data from the access table.

    What I now want to do is almost the reverse. I have a table in SQL that list any changes that have been made to specific rows (namely moving the status from query to approved, and in this table there is also a column with the corresponding access ID for the record. What I now want to create is a module that I can run in access that finds records that are in this SQL table, and updates the status column to match the status in the SQL table.

    So for example.

    This would be my Access table:

    Access ID Status
    1 Query
    2 Query
    3 Query
    4 Query
    5 Query
    6 Query
    7 Query
    8 Query

    This is the SQL table:

    SQL ID Access ID Status
    1 3 Approved
    2 7 Approved

    So I would want to write a module that updates records 3 and 7 in access to have a status of Approved.

    I hope that makes sense?

    Thanks!

  2. #2
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Office 365
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    You could just use an update query
    BUT why are you making unnecessary work by duplicating data. Just store the data in SQL Server and link to that table from Access.
    No need to keep both tables and keep synchronising
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  3. #3
    LauraSWilde is offline Novice
    Windows 10 Office 365
    Join Date
    Feb 2022
    Posts
    6
    Thanks. How do I get the update records to look at the correct record? As in to select the ID based on the column in the SQL table?

    Oh I completely agree! In my ideal world the data would all be stored in the SQL server. The problem is that the access database has been in place for donkeys years and there a bazillion things that feed from it. So my end goal would be to have it all on the SQL server, but for the time being I have a module that is set to run every night to upload the access table into the SQL table. I am then using a Power App to create my updated/approved table.

    So at the beginning of my macro that uploads the access database, I need to first update the records from the amended table. Does that make sense?

  4. #4
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    is data modified in access and updating sql server? or data modified in sql server and updating access? or does it go both ways?

    From your description it would appear to be the first one. If that is the case you could use a data macro to update sql server every time a record in access is added/changed or deleted

  5. #5
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Please have a look at the attached sample. You might want to spend some time on reading about queries\views.

    Cheers,
    Attached Files Attached Files
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  6. #6
    LauraSWilde is offline Novice
    Windows 10 Office 365
    Join Date
    Feb 2022
    Posts
    6
    Quote Originally Posted by Ajax View Post
    is data modified in access and updating sql server? or data modified in sql server and updating access? or does it go both ways?

    From your description it would appear to be the first one. If that is the case you could use a data macro to update sql server every time a record in access is added/changed or deleted
    Thanks. The data is modified in SQL and then I need to update in access.

    I am confident in updating SQL from an Access database, now I need to do the reverse.

    Thanks

    Sub Update_Requests_With_Status_Amends()


    'Opens the connnection stream to the SQL server


    Set cnn = New ADODB.Connection
    cnnstr = Connection Details


    cnn.Open cnnstr
    cnn.Execute = "SELECT * FROM dbo.Requests_Amends"


    Dim rs As DAO.Recordset
    Set rs = CurrentDb.OpenRecordset("Test")


    SQL1 = "UPDATE Test SET Status = 'Test2' WHERE ID = 4"


    DoCmd.RunSQL SQL1


    End Sub

    It does update the access table, but now I need to make SQL1 dynamic so that it will loop through the rows in the SQL table.

  7. #7
    LauraSWilde is offline Novice
    Windows 10 Office 365
    Join Date
    Feb 2022
    Posts
    6
    Thank you
    Am I right in saying that you are doing it there using a form? I am trying to do it within the macro.

    This is what I have so far. I just need to make SQ1 dynamic so that instead of being that one it loops through the rows in the SQL table.


    Sub Update_Requests_With_Status_Amends()


    'Opens the connnection stream to the SQL server


    Set cnn = New ADODB.Connection
    cnnstr = Connection Details


    cnn.Open cnnstr
    cnn.Execute = "SELECT * FROM dbo.Requests_Amends"


    Dim rs As DAO.Recordset
    Set rs = CurrentDb.OpenRecordset("Test")


    SQL1 = "UPDATE Test SET Status = 'Test2' WHERE ID = 4"


    DoCmd.RunSQL SQL1


    End Sub

  8. #8
    LauraSWilde is offline Novice
    Windows 10 Office 365
    Join Date
    Feb 2022
    Posts
    6
    Cracked it! This Now Works! I have another questions (instead of ending if there's no records I want it to return to another macro that is calling this one before it runs)

    Sub Update_Requests_With_Status_Amends()


    DoCmd.SetWarnings False


    'Opens the connnection stream to the SQL server


    Set cnn = New ADODB.Connection
    cnnstr = Connection Details


    cnn.Open cnnstr
    Set rs = cnn.Execute("SELECT * FROM dbo.Requests_Amends WHERE Date_Access_Amended Is Null")


    If rs.EOF = True Then
    End
    End If


    rs.MoveFirst


    Do While rs.EOF = False


    SQL1 = "UPDATE Test SET Status = '" & rs("status") & "' WHERE ID = " & rs("Requests_Access_ID")
    DoCmd.RunSQL SQL1


    rs.MoveNext
    Loop


    DoCmd.SetWarnings True


    cnn.Execute "UPDATE Requests_Amends SET Date_Access_Amended=GetDate() WHERE Date_Access_Amended Is Null"


    End Sub

  9. #9
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    If rs.EOF = True Then
    'name of macro here
    exit sub 'on return from other macro
    End If

    In access macros are a different method to excel macros, they are more like a 'no code/low code' option, so better to call thing by their right name - vba subs/functions

  10. #10
    LauraSWilde is offline Novice
    Windows 10 Office 365
    Join Date
    Feb 2022
    Posts
    6
    Thanks! I had actually just changed from end to Exit and it works a charm! Challenge complete! Thanks all!!

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

Similar Threads

  1. Need Update Help on Old Module
    By ortizimo in forum Modules
    Replies: 5
    Last Post: 12-04-2017, 02:13 PM
  2. Replies: 12
    Last Post: 11-11-2014, 02:10 PM
  3. Replies: 4
    Last Post: 05-16-2011, 04:58 PM
  4. Replies: 1
    Last Post: 01-22-2011, 12:23 PM
  5. Replies: 2
    Last Post: 12-22-2010, 01:46 PM

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