Results 1 to 5 of 5
  1. #1
    Join Date
    May 2004
    Posts
    159

    Unanswered: Can you help me with this SQL?

    I have this function that is a template for a dozen tables that are copied from an external network and loaded into access. It uses a select* to dump the data into a local holding table. The problem is that when extra fields are added to the external tables the code fails because the local tables do not have the new fields. Since it is a template for many tables I do not want to write new SQL specifying the specific fields for each table.
    I need to either ignore the new fields (prefferably) or if necessary add the new fields automatically, otherwise I have to wait untill the code fails, look up what fields have been added and then add them by hand (and no I am not told what and when new fields are added in the external tables). Not a very efficient way to do things but it is job security I guess. any suggestions? I'm looking for a way to say " for all fields in y copy those fields from x to y"

    Code:
    Public Sub Update_Table(sModTable As String, sRefTable As String)
      
      Dim StrSQL, strDel As String
      Dim db As DAO.Database
      
      Set db = CurrentDb
            
      'Update table
      lblStatus.Caption = "Updating Table " & sModTable & "."
      
      DoEvents
      
      strDel = "DELETE * FROM " & sModTable & ""
      db.Execute strDel
      
      StrSQL = "INSERT INTO " & sModTable & " SELECT * FROM " & sRefTable & ""
      db.Execute StrSQL
      
      db.Close
    
    End Sub
    Last edited by WilliamS; 11-29-07 at 10:06.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    you want to ignore the new fields (preferably)?

    stop writing the dreaded evil "select star" and list the columns you want

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    May 2004
    Posts
    159
    Quote Originally Posted by r937
    you want to ignore the new fields (preferably)?

    stop writing the dreaded evil "select star" and list the columns you want

    Yea but that would be the hard way since this function is called for over a dozen tables with 10 or more fields. I agree it is not good practice but it is what is there and I just want to adjust the SQL and not rewrite the program. What is the SQL way to say for each field in y that exists in x copy all the records in that field from x and insert into y?
    Last edited by WilliamS; 11-29-07 at 11:03.

  4. #4
    Join Date
    Nov 2003
    Posts
    1,487
    Since you are deleting the entire contents of the destination table (held in the sModTable variable) perhaps you can try this modified Sub instead (???):


    Code:
    Public Sub Update_Table(ByVal sModTable As String, ByVal sRefTable As String, _
                            ByVal sSrcDBPathAndFileName As String)
            
     'Update the Label Status Bar
      lblStatus.Caption = "Updating Table " & sModTable & "."
      
     'Update events. This will allow the _
      Label Status Bar to display the new _
      caption before further processing is _
      done.
      DoEvents
      
     'Delete the Destination Table... _
      Trap the Error if the table does _
      not exist in Database.
      On Error Resume Next
      
     'Delete the Table named within the sModTable _
      string variable using the DeleteObject method.
      DoCmd.DeleteObject acTable, sModTable  
      
     'If an Error occurred then we trap it, and _
      clear it. This Error happened because the _
      Table doesn't exist yet.
      If Err <> 0 Then Err.Clear
      
     'Execute the TransferDatabase method using _
      the passed info in the Sub parameters. 
      DoCmd.TransferDatabase acTable, "Microsoft Access", sSrcDBPathAndFileName, _
                             acTable, sRefTable, sModTable
      
     'Refresh the Database Window (optional)
      Application.RefreshDatabaseWindow
    
     'Refresh the Table Definitions Table
      Application.CurrentDb.TableDefs.Refresh
    
     'Update events before further processing _
      is done so refreshments can be can in place. 
      DoEvents
    
    End Sub
    If you don't care about integration then you can I suppose use this to replace the strDel and StrSQL strings within your current Sub. You will want to Error Trap the execution of DROP TABLE if the table does not exist:

    strDel = "DROP TABLE " & sModTable & ";"
    StrSQL = "SELECT * INTO " & sModTable & " FROM " & sRefTable & ";"

    With either of these methods, it doesn't matter then if new Table Fields have been added to the source because you'll end up with them as well once the update is complete.

    If you want to pull out specific fields from the source Table then you're just gonna have to do as r937 suggests, get rid of that asterisk (select star) and list the columns you want.

    .
    Environment:
    Self Taught In ALL Environments.....And It Shows!


  5. #5
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    I'd run with Rudy's idea - implement it correctly once and then stop worrying. If the original programmer had not used the SELECT * notation then you wouldn't be in this mess.

    George
    Home | Blog

Posting Permissions

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