TransferSpreadsheet into SQL Server

[JezLisle]

I have this code that when it run it will import the Spreadsheet into a TEMP table and then I want it to INSERT INTO my Live table.

The problem I have is that it will only create a TEMP table on the Access DB and not import into the SQL Server...

Where have I gone wrong?

Private Sub cmdImport_Click()
Dim cnn As ADODB.Connection
Dim strFilePath As String
Dim sQRY As String
Dim strTempTable As String
'*************************************************************************************
    Set cnn = New ADODB.Connection
    cnn.Open "Driver={SQL Server};Server=CISSQL1;Database=PODIATRY LIVE;Trusted_Connection=Yes"
'*************************************************************************************
        On Error GoTo Err
        DoCmd.SetWarnings False
        strTempTable = "HAH_AppointmentsTEMP"
        strFilePath = Me.txtFilePath
        If VBA.Len(strFilePath) <> 0 Then
            'Import the spreadsheet
            DoCmd.TransferSpreadsheet acLink, , strTempTable, strFilePath, True
'*************************************************************************************
            sQRY = "INSERT INTO HAH_StaffRota ([StaffName], [DayOfWeek], [RotaDate], [StartTime], [FinishTime]) " & _
                        "SELECT Name, Day, Date, Start Time, Finish Time " & _
                        "FROM HAH_AppointmentsTEMP "
    cnn.Execute sQRY
'*************************************************************************************
        End If
        DoCmd.DeleteObject acTable, strTempTable
        MsgBox "Data has been imported", vbExclamation, cApplicationName
        cnn.Close
        Set cnn = Nothing
    Exit Sub
Err:
        MsgBox VBA.Error$
End Sub

 

[ezfriend]

Well, your table "HAH_AppointmentsTEMP" is a local table to the Access DB when peform DoCmd.TransferSpreadsheet....

When running the INSERT statement using your cnn, it is probably looking for the "HAH_AppointmentsTEMP" table from your SQL Server which doesn't exists.

One quick way (might not be the best way) is to create a recordset and loop through your "HAH_AppointmentsTEMP" local table then insert that into SQL Server.

'................................................................
            DoCmd.TransferSpreadsheet acLink, , strTempTable, strFilePath, True
            Dim rs as Recordset
            set rs = currentdb.OpenRecordset("SELECT * FROM " & strTempTable )

            Do While rs.eof = false 

                sQRY = ""
                sQRY = sQRY & "INSERT INTO HAH_StaffRota ([StaffName], [DayOfWeek], [RotaDate], [StartTime], [FinishTime]) " 
                sQRY = sQRY & "VALUES("
                sQRY = sQRY & "'" & rs!Name & "" & "', "
                sQRY = sQRY & ..............
                sQRY = sQRY & ") "

                cnn.Execute sQRY 'insert this row.
                   rs.MoveNext
             Loop

             rs.Close
             set rs = nothing
'................................................................

 

[Banana]

Alternatively if you have linked table to SQL Server, just insert into the table the results of temp table:

INSERT INTO <SQL Server linked table> SELECT * FROM <temptable>;

(this assumes, of course, that the columns definition are consistent between both tables.

 

[JezLisle]

Well, your table "HAH_AppointmentsTEMP" is a local table to the Access DB when peform DoCmd.TransferSpreadsheet....

When running the INSERT statement using your cnn, it is probably looking for the "HAH_AppointmentsTEMP" table from your SQL Server which doesn't exists.

One quick way (might not be the best way) is to create a recordset and loop through your "HAH_AppointmentsTEMP" local table then insert that into SQL Server.

'................................................................
            DoCmd.TransferSpreadsheet acLink, , strTempTable, strFilePath, True
            Dim rs as Recordset
            set rs = currentdb.OpenRecordset("SELECT * FROM " & strTempTable )
 
            Do While rs.eof = false 
 
                sQRY = ""
                sQRY = sQRY & "INSERT INTO HAH_StaffRota ([StaffName], [DayOfWeek], [RotaDate], [StartTime], [FinishTime]) " 
                sQRY = sQRY & "VALUES("
                sQRY = sQRY & "'" & rs!Name & "" & "', "
                sQRY = sQRY & ..............
                sQRY = sQRY & ") "
 
                cnn.Execute sQRY 'insert this row.
                   rs.MoveNext
             Loop
 
             rs.Close
             set rs = nothing
'................................................................

Thanks for this it works a treat

How is it possible using this to pick the data up from a specific sheet in the workbook? The data I have is on Sheet2 Named ExportRota

so when importing its creating a TempTable how can I pick the data from ExportRota Sheet?

 

[JezLisle]

How is it possible using this to pick the data up from a specific sheet in the workbook? The data I have is on Sheet2 Named ExportRota

so when importing its creating a TempTable how can I pick the data from ExportRota Sheet?