Results 1 to 11 of 11
  1. #1
    JB510's Avatar
    JB510 is offline Advanced Beginner
    Windows 11 Office 365
    Join Date
    Jan 2023
    Location
    Ontario
    Posts
    42

    Import Data into a Table record.

    I have data in string variables. (VSerialNumber, VPartNumber) etc..


    What is the best way to import data directly into a Table record (TableT)??
    Can I do it without a form?

    Example.
    Open table record in TableT
    TableT.[SerialNumber] = VSerialNumber
    TableT.[PartNumber] = VPartNumber
    Close record

    Thanks,

  2. #2
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,861
    Use a recordset.
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

  3. #3
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    sSql = "insert into table (SerialNumber, PartNumber) values ('" & vSerialNumber & "','" & vPartNumber & "')"
    docmd.runSql sSql

  4. #4
    JB510's Avatar
    JB510 is offline Advanced Beginner
    Windows 11 Office 365
    Join Date
    Jan 2023
    Location
    Ontario
    Posts
    42
    Quote Originally Posted by ranman256 View Post
    sSql = "insert into table (SerialNumber, PartNumber) values ('" & vSerialNumber & "','" & vPartNumber & "')"
    docmd.runSql sSql
    Interesting.. I will try it but looking at the line above, it does not indicate the Table name ("TableT"). I would assume it should be ..
    sSql = "insert into TableT (SerialNumber, PartNumber) values ('" & vSerialNumber & "','" & vPartNumber & "')"
    docmd.runSql sSql

  5. #5
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Maybe, maybe not. If either of your variable data types is of type number, then no single quotes around them.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  6. #6
    JB510's Avatar
    JB510 is offline Advanced Beginner
    Windows 11 Office 365
    Join Date
    Jan 2023
    Location
    Ontario
    Posts
    42
    Quote Originally Posted by Micron View Post
    Maybe, maybe not. If either of your variable data types is of type number, then no single quotes around them.
    This idea worked for adding new records but I was confused trying to put a WHERE statement into that..
    I used a record set.

    Ex.
    Dim VSerialNumber As String
    Dim VPartNumber As String
    Dim db As Database
    Set db = CurrentDb
    Dim rs As DAO.RecordsetSTR = ("SELECT * FROM TableT WHERE PartNumber = '" & VPartNumber & "'")
    Set rs = db.OpenRecordset(STR)
    If rs.RecordCount > 0 Then 'Record already entered, edit it..
    With rs
    .Edit
    ![SerialNumber] = VSerialNumber
    ![Other Fields] = OtherVariables
    .Update
    End With
    Else 'Record not in so add it..
    Set rs = db.OpenRecordset("TableT", dbOpenTable)
    With rs
    rs.AddNew
    ![SerialNumber] = VSerialNumber
    ![Other Fields] = OtherVariables
    .Update
    End With
    End If
    Set rs = Nothing

  7. #7
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,861
    No need to set rs again, just AddNew
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

  8. #8
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    They are based on different domains? Not using code tags to maintain indentation might have caused you to miss that? Or maybe I've missed something for the same reason.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  9. #9
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,861
    Both appear to be TableT to me?
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

  10. #10
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    I was considering the data set in the first one to be a different domain because a) it's a query, which AFAIK is a domain and b) while it is selected from the same table, it's filtered so it's not the same records.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  11. #11
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,861
    Not that I am aware of?
    You select records, none are returned, so then you just add?
    I used to use a where 1=0 to get an empty recordset and then add. Then I found there is an append option only, but the logic is the same.
    Even if I was going to do that I would use another rs object.
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

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

Similar Threads

  1. How to import data to the back end table.
    By kacocal in forum Import/Export Data
    Replies: 6
    Last Post: 02-15-2016, 12:04 PM
  2. Replies: 13
    Last Post: 01-25-2016, 01:36 PM
  3. Import(copy) data from another table
    By tazzzz in forum Import/Export Data
    Replies: 16
    Last Post: 09-17-2015, 11:55 AM
  4. Possible to Import data and replace some, but not all, in table?
    By IncidentalProgrammer in forum Import/Export Data
    Replies: 2
    Last Post: 02-17-2015, 09:30 AM
  5. Replies: 4
    Last Post: 01-20-2015, 12:55 PM

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