Insert Data from an Access table to a similar table in MS SQL Server

[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.

 

[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

 

[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)

 

[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.

 

[cheekybuddha]

but it takes a LOOP to collect the thousand records.

No it doesn't - it's one SELECT call for every thousand records.

If there are fewer than a thousand records, then it's just one query.

The .GetString method builds the concatenation in a single call (very fast!) - but it will be a single query to run on the server.

As I may have mentioned, I haven't tested at all! So it may have no benefit - I'll try and do some tests this week if I get time.

 

[cheekybuddha]

ADODB.Recordset.GetString method

 

[ebs17]

Detached from previous suggestions: A normal query via Jet could also be used.

MS-Tabelle in MS-SQL Express importieren - MS-Office-Forum

Hallo zusammen, Access-Tabelle MDB soll in MS-SQL 2017 64 Bit importiert werden Beim SQL-Impo0rt ist nichts für ACCESS als Importdatei vorgesehen. Einer me Idee mfg Thomas

www.ms-office-forum.net

markusxy says: An append query takes 15 minutes, a comparable make table query only takes 18 seconds, he hadn't figured out the reason for this yet.
With this time difference, you can easily execute an additional append query from the new table to the destination table, which can then be used as a PT.

SELECT *
INTO [ODBC;Driver={ODBC Driver 17 for SQL Server};Server=xxx;Database=xxx;UID=xxx;PWD=xxxxxxxxxxxxx;].newtable
FROM AccessTable

 

[June7]

I suppose GetString() function executes a loop behind the scenes.

Another discussion https://stackoverflow.com/questions/24890695/how-to-use-local-db-table-in-a-pass-through-query

If you can figure out the connection string for Access db, try OpenDataSource or OpenRowSet in pass-through for INSERT INTO SELECT.
https://stackoverflow.com/questions/25627848/opendatasource-and-ms-access-syntax