Upsizing a Database

• sql_Reporter

  SSCrazy

  Points: 2329

  More actions

  March 7, 2007 at 7:14 am

  #90961

  I've up sized my Access database to SQL 2000, so now i see all my tables and queries(views) and stored procedures in SQL. My only concern is that i populate my tables via a DTS package. I get data from different extracts excel, txt. etc... in my DTS package i've linked all these to my Access database what i like to know will my DTS now work and populate the new tables in SQL Through Access. From my understand of upsizing i can still see the tables in access. Any help on this would be great. Am currently doing a poilit project trying to figure this out..

• DKeeling

  Old Hand

  Points: 385

  More actions

  March 8, 2007 at 1:10 am

  #693722

  Its quite east to run a DTS package from Access

   

  Const SQL_DTS_ERROR = -2147220440

  Public Sub RunDTS(strServer, strDTSName)

      ' Routine to run a DTS package from VBA

      ' Process will halt if a DTS error occurs

      ' and Display a MessageBox before Exiting

     

  On Error GoTo Err_RunDTS

      Dim pkg As New DTS.Package

     

      pkg.LoadFromSQLServer strServer, , , DTSSQLStgFlag_UseTrustedConnection, , , , strDTSName

      pkg.FailOnError = True

      pkg.Execute

     

  Exit_RunDTS:

      Set pkg = Nothing

      Exit Sub

     

  Err_RunDTS:

      If Err.Number = SQL_DTS_ERROR Then

          MsgBox "Error in DTS Package: " & strServer & " " & strDTSName & Chr(13) & " was generated by " & Err.Source & Chr(13) & Err.Description

      Else

          MsgBox Err.Number & " " & Err.Description

      End If

      Resume Exit_RunDTS

  End Sub