Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 4 of 4
  1. #1
    Regular Coder
    Join Date
    Sep 2002
    Location
    South East UK. 35 miles east of London, in sight of the River Thames.
    Posts
    300
    Thanks
    10
    Thanked 0 Times in 0 Posts

    Insert record from backup database

    Hi All,

    I have two .mdb databases on my web server. The backup holds all the records and the main database holds only the records for the last 6 weeks. I need to extract one, single record from the backup and insert it into the main database.

    I know how to insert a record using:

    INSERT INTO table_name (column1, column2,...)VALUES (value1, value2,....)

    but how do I extract the record from the backup in this format?

    Regards

    Gary

  • #2
    Senior Coder
    Join Date
    Jun 2002
    Location
    Zwolle, The Netherlands
    Posts
    1,120
    Thanks
    2
    Thanked 31 Times in 31 Posts
    uhm....

    read the values, type the insert command by hand, execute the query???

    Doesn't access hase some thingy to export a record to a text file? Use that to build the insert statement...
    I am the luckiest man in the world

  • #3
    Regular Coder
    Join Date
    Sep 2002
    Location
    South East UK. 35 miles east of London, in sight of the River Thames.
    Posts
    300
    Thanks
    10
    Thanked 0 Times in 0 Posts
    Hi Roelf,

    Sure, it can be done by hand but I have about 60 fields. I was just hoping there was a simple way to get the data into the insert statement.

    Regards

    Gary

  • #4
    Supreme Master coder! glenngv's Avatar
    Join Date
    Jun 2002
    Location
    Philippines
    Posts
    11,037
    Thanks
    0
    Thanked 250 Times in 246 Posts
    Try this:
    Code:
    dim fieldNames, fieldValues, delimiter
    
    'open connection to backup db
    
    'retrieve desired record from backup db
    
    for each field in recordset1.fields
      if fieldNames<>"" then 
        fieldNames = "," & field.name 
      else 
        fieldNames = field.name
      end if
      select case vartype(field.value)
         case 1 'null
         case 2 'integer
           delimiter = ""
         case 8 'string
           delimiter = "'"
         'add additional cases for other types
         case else
           delimiter = ""
      end select
      if fieldValues<>"" then 
        fieldValues = "," & delimiter & field.value & delimiter
      else 
        fieldValues = delimiter & field.value & delimiter
      end if
    next
    
    strSQL = "INSERT INTO table_name (" & fieldNames & ") VALUES (" & fieldValues & ")"
    
    'open connection to target db
    
    'execute strSQL
    The code only covers string and numeric fields, add as necessary. Here is the reference for the different vartypes.

    http://www.devguru.com/Technologies/...f/vartype.html

    I'm not sure if the VarType technique is applicable for ADO Field's datatype. If that doesn't work, try ADO's Field.Type property.
    Change:
    Code:
    select case vartype(field.value)
      'cases for different vartypes here
    end select
    to:
    Code:
    select case field.type
      'cases for different field.type here
    end select


  •  

    Posting Permissions

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