Insert Data from an Access table to a similar table in MS SQL Server (1 Viewer)

[nector]

I thought this was going to be very simple but I keep on getting an error message when I try to run a pass through query to insert data to similar table in MS SQL server. Please note that all the fields are the same except that the other table is in access while other one is MS SQL Server

INSERT INTO tblStaff FROM tblStaffNew

 

[ebs17]

Why do you think such a fragment would work cleanly?
Is the documentation burning so you can't look it up?

 

[nector]

Here is a clean code sir

INSERT INTO tblStaff ( EmpName, EmpPassword, UserRights, tpin, bhfId, userId, adrs, cntc, authCd, remark, useYn, regrNm, regrId, modrNm, modrId, Status )
SELECT tblStaffNew.EmpName, tblStaffNew.EmpPassword, tblStaffNew.UserRights, tblStaffNew.tpin, tblStaffNew.bhfId, tblStaffNew.userId, tblStaffNew.adrs, tblStaffNew.cntc, tblStaffNew.authCd, tblStaffNew.remark, tblStaffNew.useYn, tblStaffNew.regrNm, tblStaffNew.regrId, tblStaffNew.modrNm, tblStaffNew.modrId, tblStaffNew.Status
FROM tblStaffNew

 

[CJ_London]

Would also help if you provided the error message

but my guess is since pass through queries only work on the target db (sql server) it cannot reference tables elsewhere. So you use them for actions with the db only or return data

You would need to loop through your local table passing each field as a parameter although there are other suggestions here

How to append data from an Access table to a SQL server table via a pass-through query?

It seems like it's only possible to use a pass-through query to retrieve data from your SQL Server tables and into MS Access. But how about the other way? From an Access table to a SQL server table.

stackoverflow.com

before starting to use something you are not familiar with, you would save yourself a lot of time if you googled it so you understand what it actually does, not what you think it does

 

[nector]

Its looks like it does not recognize my access table tblstaffnew

 

[nector]

How about OPENROWSET in a passthrough query can it work here

 

[CJ_London]

How about OPENROWSET in a passthrough query can it work here

try it and see. Why not just use a linked table to sql server - what do you see as the benefit of using a passthrough query?

 

[nector]

try it and see. Why not just use a linked table to sql server - what do you see as the benefit of using a passthrough query?

I'm currently using the linked tables but committing just one takes too much time such 30 seconds

 

[CJ_London]

since you are passing data across the network and not really doing anything time consuming on the server, I doubt using a passthrough query will be any faster - but give the suggested methods a try. More likely to be time taken making the connection to the server and/or network performance

 

[GPGeorge]

PMFJI:

I'm going to repeat what's already been explained because apparently it didn't sink in yet.

You cannot use a pass-thru query to append data directly from a local Access table to a SQL Server table.

The local Access table called "tblStaffNew" is in Access. SQL Server processes the SQL statement sent to it entirely on SQL Server. That means SQL Server has no idea what "tblStaffNew" is.

How many records are in the table called "tblStaffNew"?

What indexes exist on "dbo.tblStaff" in SQL Server?

How often do you need to do this data transfer?

You might need to use VBA and ADO to append the data if there is a significant number of records to append or if the frequency of appends being required is very large.

 

[ebs17]

You would probably first have to talk about exactly which path (line, bandwidth) the backend is connected to the frontend.

 

[Pat Hartman]

As the others have already mentioned. Since the data to be appended is in Access, the most efficient would be a simple Access append query that links to the Access table and the Server table. The loop will be much slower if you have more than a few records. When you only have a few records, it makes no difference what method you use.

 

[cheekybuddha]

If you really want to use a pass-through, you can do something like this (completely untested) code.

It creates an INSERT statement from your table tblStaffNew in Access which it then executes as pass-through on the server.

First, since I don't know your tables, I have assumed that tblSatffNew has a primary key field called 'ID'. You will have to change this as required.

This is important since the VALUES function in T-SQL has a maximum of 1000 records. So if you are trying to insert more than 1000 new staff records, then you have to batch it and execute more than one pass-through. So the code tries to check how many records are in tblStaffNew and if there are more than 1000 it will limit it to that number of records per batch. Ordering by ID enables the code to select the next batch of records in the next pass of the loop.

Since you are building an SQL statement strings and dates must be properly delimited. There are two helper functions for this, SQLStr() and SQLDt() which you will need to copy into a standard module. These functions must be applied in the SQL that selects the records from tblStaffNew. I tried to make a guess at which fields are strings, but you will need to adjust according to the datatypes of your actual fields. See const SQL_STAFF_NEW for how it should be done. (I didn't guess that any fields were dates, but if any are then you will need to wrap with SQLDt())

The SQLStr() function takes a second argument if any of your string data is unicode so that it will add the 'N' before the quoted string value. If you are not using unicode in your text values then you can safely ignore this.

Did I mention that I haven't tested any of this, so quite likely it won't work on first go. Nor did I add any error handling.

Anyway, these are the helper functions:

Function SQLStr( _
  vString As Variant, _
  Optional blUnicode As Boolean = False, _
  Optional blQuote As Boolean = True _
) As String

  Dim strRet As String

  If Not IsNull(vString) Then
    strRet = Replace(CStr(vString), "'", "\'")
    If blQuote Then
      strRet = IIf(blUnicode, "N", vbNullString) & "'" & strRet & "'"
    End If
  Else
    strRet = "NULL"
  End If
  SQLStr = strRet

End Function

Function SQLDt( _
  vDate As Variant, _
  Optional SQL_DATE_FORMAT As String = "\'yyyy\-mm\-dd hh:nn:ss\'" _
) As String

  Dim strRet As String

  If IsDate(vDate) Then
    strRet = Format(vDate, SQL_DATE_FORMAT)
  Else
    strRet = "NULL"
  End If
  SQLDt = strRet

End Function

And this is the function to do the inserts:

Function AddNewStaffToServer() As Boolean

  Const adCmdText     As Integer = 1, _
        adClipString  As Integer = 2, _
        MAX_ROWS      As Integer = 1000, _
        MAX_ID        As String = "[[MAX_ID]]"

  Const SQL_INSERT    As String = _
    "INSERT INTO tblStaff (" & vbNewLine & _
      "EmpName, EmpPassword, UserRights, tpin, bhfId, userId, adrs, cntc, " & vbNewLine & _
      "authCd, remark, useYn, regrNm, regrId, modrNm, modrId, Status" & vbNewLine & _
    ") VALUES " & vbNewLine

  Const SQL_STAFF_NEW As String = _
    "SELECT TOP " & MAX_ROWS & " " & vbNewLine & _
      "SQLStr(EmpName), SQLStr(EmpPassword), SQLStr(UserRights), tpin, bhfId, userId, adrs, cntc, " & vbNewLine & _
      "authCd, SQLStr(remark), useYn, regrNm, regrId, modrNm, modrId, SQLStr(Status)" & vbNewLine & _
    "FROM tblStaffNew " & vbNewLine & _
    "WHERE ID > " & MAX_ID & " " & vbNewLine & _
    "ORDER BY ID;"

    Dim values        As String, _
        strCN         As String, _
        numRecs       As Integer, _
        iterations    As Integer, _
        lastID        As Long, _
        i             As Integer

  numRecs = DCount("*", "tblStaffNew")
  iterations = (numRecs \ MAX_ROWS) + 1
  With CurrentDb
    strCN = .TableDefs("tblStaff").Connect
    With .CreateQueryDef(vbNullString)
      .Connect = strCN
      .ReturnsRecords = False
      For i = 1 To iterations
        With CurrentProject.Connection.Execute(Replace(SQL_STAFF_NEW, MAX_ID, lastID), adCmdText)
          If Not .EOF Then
            values = .GetString(adClipString, MAX_ROWS, ", ", ")," & vbNewLine & "  (", "NULL")
          End If
          .MoveLast
          lastID = .Fields("ID")
          .Close
        End With
        If Len(values) Then
          .SQL = SQL_INSERT & "  (" & values & ");"
          Debug.Print .SQL
          .Execute dbFailOnError
          values = vbNullString
        End If
      Next i
    End With
  End With

  AddNewStaffToServer = Err = 0

End Function

hth

 

[Pat Hartman]

If you really want to use a pass-through, you can do something like this (completely untested) code.

I'm pretty sure the only reason for wanting a pass-through specifically is if it would be more efficient than an Access query against linked tables. There is nothing inherently good or bad about a pass-through query. However, a loop, for more than a small number of records, is always the slowest option so jumping through hoops to create a pass-through query that runs one query per row inserted seems a bit wasteful to simply avoid using an Access query.

I still remember the lesson I learned in one of my earliest Access apps. It was a conversion. It was taking customer data from 8 other applications, standardizing the code values, and appending the data to a single table. The original version of the code did two very bad things.
1. It used dlookups to convert the code values (3 fields were converted this way)
2. It used a VBA loop.

WHY ? - Those were the examples I found and I had no way of assessing them. In COBOL, I would still have used the code loop but I would have used arrays for the three conversions rather than domain functions since the lookups were less than 100 rows each. I tested with a few records and it seemed to work so I let 'er rip on 109,000 records which was the first customer file. I canceled it after an hour but the file looked fine so I deleted all the data and started again. It ran for 4.5 hours. I decided that was unacceptable and sat back and thought about what the problem would be. I got rid of the loop and used an append query and replaced the domain functions with left joins to the lookup tables. Took, wait for it - 2.6 minutes. I never broke the code up to see which thing had the biggest impact. I'm guessing that it was getting rid of the code loop. The lookups were pretty tiny tables and Access would have converted them into arrays for efficiency.

 

[cheekybuddha]

However, a loop, for more than a small number of records, is always the slowest option so jumping through hoops to create a pass-through query that runs one query per row inserted seems a bit wasteful to simply avoid using an Access query.

Did you even read the code?

It doesn't loop like you think. It performs only a single pass-through per every 1000 records.

If you don't understand the code you need only ask.

 

[June7]

Missing &:

.SQL = SQL_INSERT & " (" & values & ");"

Also, not sure about the With nesting. Which object will .EOF, .MoveLast, .Fields, .GetString, .Close refer to?

 

[cheekybuddha]

Missing &:

.SQL = SQL_INSERT & " (" & values & ");"

Corrected - thank you June!

Also, not sure about the With nesting.

I haven't tested it (might do so later if I get a chance), but the With blocks should scope to to within themselves.

So, the .EOF, .MoveLast, .Fields, .GetString, .Close belong to the ADODB.Recordset (from CurrentProject.Execute).

.Connect, .ReturnsRecords, .SQL and .Execute belong to the QueryDef (from CurrentDb.CreateQueryDef)

 

[Pat Hartman]

For i = 1 To iterations

I did read the code.
Looks like a loop to me But I didn't read far enough to see the one insert per thousand records so the loop isn't as bad as it looked but Action queries are always the fastest method.

 

[cheekybuddha]

but Action queries are always the fastest method.

It is an action query, just performed on the server (which would probably be even faster than from Access) of up to 1000 records at a time, which is the maximum you can do in a single hit using VALUES.

 

[Pat Hartman]

I didn't mean to argue with you about this but it takes a LOOP to collect the thousand records. That is what I was trying to point out. I wouldn't create a loop just so I could make a pass-through query. The code may be clever but I would simply use an Access append query.