Results 1 to 4 of 4

Thread: access function

  1. #1
    Join Date
    Jul 2004
    Posts
    214

    Unanswered: access function

    Hello All, I recently upsized a database I inherited backend to Sql Server 2008. I have a access 2003 frontend. One of my functions include multiple import statements and multiple update statements. I planned on splitting them up into their own routines. But; now that it's a Sql backend; should I use Sql Statements written out and if yes; how do I create the multiple update statements? I tried the Sql select statements but I keep getting "end expected statement...." Should this be in a Stored Procedure or a simple Function and call diffenent routines? There are three different tables involved. I attached the function:
    Code:
     Public Function ImportText()
     On Error GoTo Err_ImportText
     
    
    If MsgBox("You are about to run the import for all three modules. Are you sure spreadsheets are formated correctly:)?", vbYesNo + vbQuestion, "Confirm Import") = vbYes Then
    
    
    'Delete records from table
                
    '            DoCmd****nSQL ("delete from tblSP_SHIPMENT_temp")
    '            DoCmd****nSQL ("delete from tblDTF_SHIPMENT_temp")
                DoCmd****nSQL ("delete from tblLTL_SHIPMENT_temp")
            
                
    'Import Text files into corresponding tables
    ''DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel8, _
    ''"tblSP_SHIPMENT_temp", "C:\Users\JEN\Desktop\SMALLPACKAGEIMPORT.xls", True
    ''
    '''DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel8, _
    '''"tblDTF_SHIPMENT_TEMP", "C:\Users\JEN\Desktop\DUTIESANDTAXESIMPORT.xls", True
    ''
    ''DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel8, _
    ''"tblLTL_SHIPMENT_TEMP", "C:\Users\JEN\Desktop\LESSTHANTRUCKLOADIMPORT.xls", True
    '
    'DoCmd.OpenQuery "qrySP_Duplicates"
    'DoCmd.OpenQuery "qryDTF_Duplicates"
    'DoCmd.OpenQuery "qryLTL_Duplicates"
    '
    'DoCmd.OpenQuery "qrySPUpdate_carrier"
    'DoCmd.OpenQuery "qrySPUpdate_zipcode"
    'DoCmd.OpenQuery "qrySPCntryRegUpdate"
    'DoCmd.OpenQuery "qrySPInvoice_append"
    'DoCmd.OpenQuery "qrySPShip_TEMP_Append"
    DoCmd.OpenQuery "qryLTLUpdate_carrier"
    DoCmd.OpenQuery "qryLTLUpdate_zipcode"
    DoCmd.OpenQuery "qryLTLCntryRegUpdate"
    DoCmd.OpenQuery "qryLTLInvoice_append"
    DoCmd.OpenQuery "qryLTLShip_TEMP_Append"
    'DoCmd.OpenQuery "qryDTFUpdate_carrier"
    'DoCmd.OpenQuery "qryDTFUpdate_zipcode"
    'DoCmd.OpenQuery "qryDTFCntryRegUpdate"
    'DoCmd.OpenQuery "qryDTFInvoice_append"
    'DoCmd.OpenQuery "qryDTFShip_TEMP_Append"
    
    MsgBox "Import Completed"
    
    End If
    Exit_ImportText:
        Exit Function
    
    Err_ImportText:
        MsgBox Str(Err.Number) & " - " & Err.Description
        Resume Exit_ImportText
    
    End Function
    I welcome any help or/and suggestions.

  2. #2
    Join Date
    Sep 2001
    Location
    Chicago, Illinois, USA
    Posts
    601
    Do it in a stored procedure.

    A single stored procedure can contain multiple SQL statements.

    From within Access, you can call a stored procedure as if you were calling a pass-through query object. If the results of the stored procedure is not a recordset (resulting from a SELECT) then be sure to set the ReturnRecords property of the query to NO.
    Ken

    Maverick Software Design

    (847) 864-3600 x2

  3. #3
    Join Date
    Jul 2004
    Posts
    214
    Ok. Do I create the stored procedure in the SQL backend? And how do I call it on a form in the access front end? You mentioned "pass thru queries" The users are going to be using a form and clicking on a command button to run these actions.
    Thanks

  4. #4
    Join Date
    Sep 2001
    Location
    Chicago, Illinois, USA
    Posts
    601
    Since this is really an Access question, why don't we take this off-line and why don't you give me a call at the number on my signature line.
    Ken

    Maverick Software Design

    (847) 864-3600 x2

Posting Permissions

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