none
Transfer Data from Local Access Table to Linked SQL Server Table RRS feed

  • Question

  • I would like to find an efficient solution to moving data from a local Access table into a Linked SQL Server table.  Running a simple Append through Access has proven to be a terrible solution, with a 10+ minute run time for about 200,000 records.  

    I am looking into creating a stored procedure that will take in the local Access table as a table valued parameter, then insert all of those records into the SQL table.  I have created the following stored proc:

    CREATE TYPE MOR.RMAZATableType AS TABLE (
    	PostingDate smalldatetime,
    	RMA varchar(12));
    go
    
    
    
    CREATE PROCEDURE MOR.USP_InsertRMAData
    	@TVP RMAZATableType READONLY
    	AS
    
    Set NOCOUNT ON
    Insert Into mor.tblRMAZA (PostingDate,RMA)
    SELECT * FROM @TVP
    Return;
    Go

    I am fairly new to SQL Server, so I believe there is probably an error in the above.  

    I then attempt to pass the matching recordset via stored procedure call in Access:

    Sub USP_InsertTest(rst As Recordset)
      Dim conn As New ADODB.Connection, cmd As New ADODB.Command
      Set conn = OpenSQLConnection
      Set cmd = OpenStoredProc("MOR.usp_InsertRMAData", conn)
      cmd("@TVP") = rst     <----------------------
      cmd.Execute
      Set cmd = Nothing
      CloseSQLConnection conn
    End Sub

    I get an error here (cmd("@TVP") = rst).  "Arguments are of the wrong type, are out of acceptable range, or are in conflict with one another."

    Please help!!

    I am using SSMS 2012 and MS Access 2010.


    Monday, July 13, 2015 8:16 PM

All replies

  • Hi,

    Base on the error message, it means the RecordSet is out of acceptable range, in my opinion, we can’t access the recordset object on the stored procedure.

    For this requirement, I suggest that the simple way is that you could use SQL Server import and export wizard to import data to the SQL Server database.

    # Run the SQL Server Import and Export Wizard

    https://msdn.microsoft.com/en-us/library/ms140052(v=sql.110).aspx

    On the other hand, you could try to achieve that through the SQL statement with OPENDATASOURCE syntax.

    # OPENDATASOURCE (Transact-SQL)

    https://msdn.microsoft.com/en-IN/library/ms179856.aspx

    Regards

    Starain


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Wednesday, July 15, 2015 3:01 AM
    Moderator
  • Hello Starain,

    Thank you for the response.  When attempting to run an OPENDATASOURCE, I receive the below message:

    "Ad hoc access to OLE DB provider 'Microsoft.ACE.OLEDB.12.0' has been denied. You must access this provider through a linked server."

    We have not been successful in using the sp_addlinkedserver technique.

    Running the SQL Server Import and Export Wizard works great, but I am not sure on how to implement.  Our users will be using an Access database; from there, they will select an Excel Spreadsheet to append to a linked SQL table.  Ideally, I would like to move the data directly from the spreadsheet to the SQL table (I was originally doing a DoCmd.TransferSpreadsheet method to an identical Local table first then moving that data to the SQL table because it was taking significantly longer to do the DoCmd.TransferSpreadsheet directly to the SQL table).  Am I able to save a package that allows for user input on where the source is?  

    Thanks!

    Wednesday, July 22, 2015 3:23 PM
  • Hi pigeonkiller,

    For that error, you could refer to this article:

    # How to use the DisallowAdHocAccess setting to control access to linked servers

    https://support.microsoft.com/en-us/kb/327489

    >> Running the SQL Server Import and Export Wizard works great, but I am not sure on how to implement

    Since it works great, what do you mean how to implement?

    >> Am I able to save a package that allows for user input on where the source is? 

    You could let user to specify the file path through InputBox method or display the open dialog box to let user to select file by using GetOpenFilename method.

    Regards

    Starain


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Thursday, July 23, 2015 3:00 AM
    Moderator