Results 1 to 4 of 4
  1. #1
    Join Date
    Nov 2007
    Posts
    2

    Unanswered: copy content of a table from one access database to another access database tabel

    I am trying to copy the content of tabel A from database A into tabel B in database B. so when ever the content of tabel A changes happens to tabel B as well. I am new to access, if anyone know this please help.

  2. #2
    Join Date
    Nov 2003
    Posts
    1,487
    If the table structure is the same....why not just Link Table A from Database A to Database B

    Menu Item: File | Get External Data | Link Tables...

    To do this in Code put something like this into your Start Up Form's OnOpen event:

    DoCmd.TransferDatabase acLink, "Microsoft Access", pathAndFileNameOfDatabaseA, _
    acTable, "tableNameInDB_A", "tableNameInDB_A"


    (Change the items in red to their proper names)

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


  3. #3
    Join Date
    Nov 2007
    Posts
    2

    copy content of a table from one access database to another access database tabel

    what i have is database A (archive.mdb ) which gets replaced everytime there is new data. I am saying the entire mdb file will get replaced but still contains the same names. Now inside the mdb there is tabel A which has all of its column as a Text data type. One column is Date but in a Text data type. That is why i am trying to create a new database with a new tabel that contains all the content in tabel A but the Date column will have a Date/Time Data type. how do I go by implementing this procedure.

  4. #4
    Join Date
    Nov 2003
    Posts
    1,487
    Ok...First off, Back Up your Database.

    Now, I'm gonna give you a couple of Functions you may find useful (or not). Just copy and paste them into into a Database Code Module.

    The first Function (ChangeFieldType) is a simple little procedure for modifying the Data Type for a Field in a Table. There are other more extensive ways to do this but I'm just going to supply a simple one:

    Code:
    Public Function ChangeFieldType(strTableName As String, strFieldName As String, strFieldType As String) As Long
       'DAO 3.x required.
       'This Function will change the DataType of a supplied field 
       'within the supplied Table with the supplied DataType.
       '
       'Parameters:
       '===========
       'strTableName = The name of the Table to deal with.
       'strFieldName = The name of the Table Field to deal with.
       'strFieldType = The Data Type you want to change the above field to. See list below.
    
       ' 1 = Success
       ' 0= Failure
       '
       'How To Use:
       '===========
       ' Call ChangeFieldType("tblName","fldName","TEXT(100)")
       '-----------------------------------------------------------------------------
       '
       'Data Types:
       '===========
       ' AutoNumber          {Designated as a AutoNumber type - Can Not Be Set}
       ' Byte                {Designated as a Number type}
       ' Integer             {Designated as a Number type}
       ' Long                {Designated as a Number type}
       ' Single              {Designated as a Number type}
       ' Double              {Designated as a Number type}
       ' Float               {Designated as a Number type - Field Size is Listed as Double}
       ' GUID                {Designated as a Number type - Listed as 'Replication ID'}
       ' Decimal             {Designated as a Number type - Can Not Be Set}
       ' Text or Text(size)  {Designated as a Text type}
       '                     {Default size is 255 which is maximum}
       ' Char                {Designated as a Text type}
       '                     {Default size is 255 which is maximum}
       ' Memo                {Designated as a Memo type - Max size is unlimited}
       ' Number              {Designated as a Number type}
       '                     {Default is Double}
       ' Currency            {Designated as a Currency type}
       ' YesNo               {Designated as a Yes/No type}
       ' Date                {Designated as a Date/Time type}
       ' TimeStamp           {Designated as a Date/Time type}
       ' Time                {Designated as a Date/Time type}
       ' Binary              {Designated as a Binary type}
       ' VarBinary           {Designated as a Binary type}
       ' OLEObject           {Designated as a OLE Object type}
       ' LongBinary          {Designated as a OLE Object type}
       '-----------------------------------------------------------------------------
       
       On Error GoTo Error_ChangeFieldType
       Dim db As Database
       Dim strSQL As String
       strSQL = "ALTER TABLE [" & strTableName & "] ALTER COLUMN [" & strFieldName & "] " & UCase(strFieldType)
    
       Set db = CurrentDb
       db.Execute strSQL
       ChangeFieldType = 1
       
    Exit_ChangeFieldType:
       db.Close
       Set db = Nothing
       Exit Function
       
    Error_ChangeFieldType:
       ChangeFieldType = 0
       Err = 0
       Resume Exit_ChangeFieldType
    End Function
    This next Function (DoesTableExist) simply checks to see if a Table exists in either the local DB or and external one. Again, copy & paste it into a Database Code Module:

    Code:
    Public Function DoesTableExist(TableName As String, Optional DBName As String) As Boolean
       'DAO 3.x required.
       'This Function will see if a Table exists in either en external or
       'loacal Database.
       '--------------------------------------------------------------------
       'Parameters:
       '===========
       'TableName = The name of the Table you would like to see if it Exists.
       'DBName    = (optional) - The Path & File Name of the Database to look
       '            into for the Table. If this is not supplied then the DB
       '            checked will be the DB that Called this Function.
       ' ---------------------------------------------------------------------
       ' Returns:
       ' True (-1) if Table Exists (logged as [1]).
       ' False (0) if table does not Exist.
       ' ---------------------------------------------------------------------
    
       Dim db As Database
       Dim i As Integer
       Dim tbl As String
       
       If DBName <> "" Then
          Set db = OpenDatabase(DBName)
       Else
          Set db = CurrentDb
       End If
       
       db.TableDefs.Refresh
       tbl = Trim(TableName)
       If Left$(tbl, 1) = "[" And Right$(tbl, 1) = "]" Then
          tbl = Mid$(tbl, 2, Len(tbl) - 2)
       End If
       For i = 0 To db.TableDefs.Count - 1
           If tbl = db.TableDefs(i).Name Then
               'Table Exists
               DoesTableExist = True
               Exit For
           End If
       Next i
       db.Close
       Set db = Nothing
    End Function
    Now that we have that out of the way...copy & paste the following code into the OnClick event of a Command button (or wherever you want to use it):

    Code:
       'If you want to delete the table before you copy the new one
       'into this DB then use either one of the two methods shown in
       'the commented lines below. If the table already exists and
       'you don't delete it then an additional table is added to the
       'Database with the same name but incremented with a number at
       'the end of the name (ie: TestTable, TestTable1, TestTable2,
       'and so on. TestTable being the first table copied into the DB.
       'In this example, Method 2 is used.
       '
       'Method 1
       'If DoesTableExist("yourTableName") = True Then
       '   CurrentDb.Execute "DROP TABLE [yourTableName]"
       'End If
       '
       'Method 2
       'See if the Table already exists within this
       'Database. If it does then Delete it. See
       'the DoesTableExist Function Details.
       If DoesTableExist("yourTableName") = True Then
          DoCmd.DeleteObject acTable, "yourTableName"
       End If
    
       'Copy the Table (Structure & Data) from the External Database
       'and place it into this Database.
       DoCmd.TransferDatabase acTable, "Microsoft Access", "pathAndFileNameOfExternalDB", _
                              acTable, "yourTableName", "yourTableName"
       
       'Update the TableDefs Collection
       Application.CurrentDb.TableDefs.Refresh
       
       'Change the DataType for our desired Table Field.
       'See Function Details.
       Call ChangeFieldType("yourTableName", "theTableFieldName", "Date")
       
       'Open to have a peek at the Newly Recieved & Modified
       'Table. (This of course is not a requirement :) )
       DoCmd.OpenTable "yourTableName"
    Be sure to change the items in Red-Italic to their proper names.
    whew....hope that works. Looks like a lot of code but it's more comments than code. You should throw in Error traping as well.

    .
    Last edited by CyberLynx; 11-22-07 at 03:03.
    Environment:
    Self Taught In ALL Environments.....And It Shows!


Posting Permissions

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