Results 1 to 5 of 5
  1. #1
    Join Date
    Dec 2012
    Posts
    31

    Unanswered: Insert value in one table to another

    Guys i have a problem

    What's the other syntax in inserting a value from one table to another table using VbAcess without using this kind of syntax "INSERT INTO Employee SELECT * FROM HRData

    The fields of my table Employee are:
    EmployeeID
    Lastname
    Firstname
    Firstname
    Gender
    Status

    The fields of my HRData are same with my Employee

    Any reply would be much appreciated...tnx in advance...

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Quote Originally Posted by djrods View Post
    Guys i have a problem

    What's the other syntax in inserting a value from one table to another table using VbAcess without using this kind of syntax "INSERT INTO Employee SELECT * FROM HRData
    There id none in SQL, as far as I know, except if the destination table does not exists. In this case, you can use:
    Code:
    SELECT * INTO Employee FROM HRData
    In VBA, you can use:
    Code:
    Sub CopyRows(ByVal Source As String, ByVal Destination As String)
    
        Dim dbs As DAO.Database
        Dim rstS As DAO.Recordset
        Dim rstD As DAO.Recordset
        Dim i As Long
        
        Set dbs = CurrentDb
        Set rstS = dbs.OpenRecordset(Source, dbOpenSnapshot)
        Set rstD = dbs.OpenRecordset(Destination, dbOpenDynaset)
        With rstS
            Do Until .EOF
                rstD.AddNew
                For i = 0 To .Fields.Count
                    rstD.Fields(i).Value = .Fields(i).Value
                Next i
                rstD.Update
                .MoveNext
            Loop
            .Close
        End With
        rstD.Close
        Set rstS = Nothing
        Set rstD = Nothing
        
    End Sub
    But it is terribly slow when compared to a SQL instruction.
    Have a nice day!

  3. #3
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Why are you copying the same data from HRData to employee

    SQL is the way you move data around in a SQL database such as Access/JET
    if you are going to develop anything in Access you need to make an effort to understand SQL, what it is and how it works.

    one of the biggest things to get to grips with is that it is a set based language (ie it process SETS of data that match criteria
    if no criteria are supplied it applies to all rows
    if the criteria are so restricted then it may apply to only one row.

    inside a db it is the fastest way of manipulating data
    I'd rather be riding on the Tiger 800 or the Norton

  4. #4
    Join Date
    Dec 2012
    Posts
    31
    My main table is really the Employee. The HRData is just a temporary table coz i'm making a program that can import the Data from HR to my Database. I'm using it for me to find easy in updating and inserting data from my Employee table by using the dummy table. I'm new to MSAccess language.coz i'm always using PHP. But the problem is i dont know what's the syntax in inserting from one table to another...

  5. #5
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    If it's for filling a temporary table, the simplest way consists in using the SELECT ... INTO ... FROM syntax. All details on how to use this SQL expression can be found in Access help or at: SELECT
    Have a nice day!

Posting Permissions

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