Results 1 to 8 of 8
  1. #1
    Thom2467 is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Sep 2017
    Posts
    4

    Set a table column to NULL after imported to excel

    Hi.

    I'm attempting to import an Access table into an Excel workbook where i run different macros.

    I already have the code to import my Access table into Excel running smoothly, however i would like to set the value of one column in the access table to blank (NULL) after importing to Excel.
    Is it possible to incorporate something like Update myTable set MyColumn = NULL into my current code to make this happen?

    I'm unsure of how to do this as i know very little of Access.
    Any help would be appreciated.

    My current code is:
    Public sub RefreshData()
    On Error GoTo SubError
    Const DbLoc As String = "V:\Erhverv\Revisorbesvarelse\Revisorbesvarelse.ac cdb"
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim xlBook As Workbook
    Dim xlSheet As Worksheet
    Dim recCount As Long
    Dim SQL As String

    Set xlBook = ActiveWorkbook
    Set xlSheet = xlBook.Worksheets("Importeret data")

    xlSheet.Range("A2:M1500").ClearContents

    Application.StatusBar = "Connecting to an external database..."
    Application.Cursor = xlWait

    Set db = OpenDatabase(DbLoc)

    SQL = "SELECT * " _
    & "FROM tbl_revisor " _

    Set rs = db.OpenRecordset(SQL, dbOpenSnapshot)

    Application.StatusBar = "Writing to spreadsheet..."
    If rs.RecordCount = 0 Then
    MsgBox "No data retrieved from database", vbInformation + vbOKOnly, "No Data"
    GoTo SubExit
    Else
    rs.MoveLast
    recCount = rs.RecordCount
    rs.MoveFirst
    End If

    xlSheet.Range("A2").CopyFromRecordset rs
    Application.StatusBar = "Update complete"



    SubExit:
    On Error Resume Next
    Application.Cursor = xlDefault
    rs.Close
    Set rs = Nothing
    Set xlSheet = Nothing
    Set xlBook = Nothing
    Exit Function

    SubError:
    Application.StatusBar = ""
    MsgBox "RefreshData - UpdateData VBA error: " & vbCrLf & Err.Number & " = " & Err.Description

    Resume SubExit




    End sub





  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    run a query, set FIELD = NULL.

  3. #3
    Thom2467 is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Sep 2017
    Posts
    4
    How and where do I incorporate this into the code above?

  4. #4
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    right before:
    xlSheet.Range("A2").CopyFromRecordset rs
    Application.StatusBar = "Update complete

    put
    docmd.openquery "quSetFld2Null"

    but if you do not want to set the table to null
    then set the column in excel then:

    columns("c:c").clearcontents

  5. #5
    Thom2467 is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Sep 2017
    Posts
    4
    This does not work. Im running the macro in excel

  6. #6
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    You have an error in the code
    Code:
    SubExit:
        On Error Resume Next
        Application.Cursor = xlDefault
        rs.Close
        Set rs = Nothing
    
        db.Close                '<<-- I think you also need these two lines
        Set db = Nothing        '<<-- I think you also need these two lines
    
        Set xlSheet = Nothing
        Set xlBook = Nothing
        Exit Function   '<<--should be EXIT SUB, not Function
    
    SubError:
        Application.StatusBar = ""
        MsgBox "RefreshData - UpdateData VBA error: " & vbCrLf & Err.Number & " = " & Err.Description
    
        Resume SubExit
    
    End Sub


    DISCLAIMER
    I have never tried to automate Access from Excel, but you might try this:
    Code:
    '<snip>
        xlSheet.Range("A2").CopyFromRecordset rs
        Application.StatusBar = "Update complete"
    
    'Change YourFieldName to the name of the field you want to set to Null
        db.Execute "UPDATE tbl_revisor SET tbl_revisor.YourFieldName = Null;"
    
    SubExit:
        On Error Resume Next
        Application.Cursor = xlDefault
    '<snip>

  7. #7
    Thom2467 is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Sep 2017
    Posts
    4
    Thank you! Works like a charm.

  8. #8
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Wonderful!

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

Similar Threads

  1. Replies: 4
    Last Post: 03-19-2015, 06:14 PM
  2. Replies: 4
    Last Post: 11-27-2014, 10:20 AM
  3. Replies: 6
    Last Post: 10-09-2014, 12:41 PM
  4. Replies: 1
    Last Post: 06-11-2014, 11:38 AM
  5. Linking Access Table with already imported External Data (Excel)
    By izzarshah in forum Import/Export Data
    Replies: 1
    Last Post: 07-29-2010, 09:40 AM

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