How to import table from another database


DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 13 of 13

Thread: How to import table from another database

  1. #1
    Join Date
    May 2008
    Posts
    6

    How to import table from another database

    Hi!.. Is there a code in vb6 on how to import a table from another database. Thanks

  2. #2
    Join Date
    Aug 2004
    Location
    Orange, California
    Posts
    1,263
    No not directly. Usually the Import feature is part of the Database system you are using. For example MS Access has an Import feature, so you could open up your database in MS Access and have it import the table. You could also use Automation to have VB startup Access in the background, and have it tell Access to Import the Table. You need to look up the Import features of the database system you are using: MS Access, SQL Server, Oracle, MySQL, etc..

  3. #3
    Join Date
    May 2008
    Posts
    6
    I think thats a good idea... how? Help. Pls

  4. #4
    Join Date
    Aug 2004
    Location
    Orange, California
    Posts
    1,263
    You need to be a little more specific. Each database system has it's own way of importing. Oracle keeps all of its help files online, so you could go to their website and lookup Importing from there.
    MS Access has it's own Help files, so just click on the Help menu and search on Import from there.
    You would be better off going to the Database Forum and asking a more specific question, like:
    How do I import a table from an Oracle database to a SQL Server database?
    ...or...
    How do I import a Table from one MS Access database to another MS Access database?
    ...etc...

    If you need help on Automation, for MS Access, I can help you with that in this forum.
    In MS Access you can go to the Tables Tab, right click on an empty spot, and select the Import option on the popup menu.
    SQL Server has a manager application.
    Oracle uses a utility called SQL Plus
    --Don't know about MySQL--

  5. #5
    Join Date
    Apr 2007
    Location
    Sterling Heights, Michigan
    Posts
    8,666
    Quote Originally Posted by Ron Weller
    --Don't know about MySQL--
    Me either, but in case someone is interested, I did find this on the MySQL site.
    I don't answer coding questions via PM or Email. Please post a thread in the appropriate forum section.
    Please use [Code]your code goes in here[/Code] tags when posting code.
    Before posting your question, did you look here?
    Got a question on Linux? Visit our Linux sister site.
    Modifications Required For VB6 Apps To Work On Vista

  6. #6
    Join Date
    May 2008
    Posts
    6
    Hi Ron Weller,

    How do I import a Table from one MS Access database to another MS Access database?

    Ive done it already manually. Right Click and then import... etc etc.
    I want it to be automated. Everytime i run my payroll program msaccess will delete the table and import the updated table. How do i do that using visual basic code.

    I have HRIS sys [HRIS DATABASE] and Payroll sys [PAYROLL DATABASE]. The EmpayData from HRIS will be imported to payroll database. Everytime an employee data has been modified in hris especially the bank acct no. i always manually import the emppaydata table manually. I want the program in payroll to import the emppaydata before it is loaded.

    Thanks

  7. #7
    Join Date
    May 2008
    Posts
    6
    if u have any suggestion to make my program easier ill greatly appreciate it. Thanks again

  8. #8
    Join Date
    Aug 2004
    Location
    Orange, California
    Posts
    1,263
    Here is how you could Automate Access to do the Import:
    Code:
    Sub ImportMonthlyOrders()
    ' Dim acApp As Access.Application 'Early Binding -  makes development easier but requires reference to Access Library
      Dim acApp As Object             'Late Binding - requires no library reference so switch to this after development
        
      'Create new hidden instance of Access.
      Set acApp = CreateObject("Access.Application")
      'Uncomment this when debugging to Show this instance of Access.
      'acApp.Visible = True
      With acApp
        'Open the Corporate Database
        .OpenCurrentDatabase "C:\My Documents\Corporate.mdb"
        'Import Orders Table from the MonthlyOrders Database.
        .DoCmd.TransferDatabase , , "C:\My Documents\MonthlyOrders.mdb", , "tblOrder"
        'You can also change the name of the table when you Import it
    '   .DoCmd.TransferDatabase , , "C:\My Documents\MonthlyOrders.mdb", , "tblOrder", "tblOrders_" & Format(Month(Now), "00")
      End With
      acApp.Quit
      Set acApp = Nothing
    End Sub

  9. #9
    Join Date
    Aug 2004
    Location
    Orange, California
    Posts
    1,263
    Quote Originally Posted by lantis
    Ive done it already manually. Right Click and then import... etc etc.
    I want it to be automated. Everytime i run my payroll program msaccess will delete the table and import the updated table. How do i do that using visual basic code.
    Are you trying to do this in Access using VBA or are you using VB6?
    The example code I posted before is for VB6. The code would be much simpler in Access.

  10. #10
    Join Date
    Aug 2004
    Location
    Orange, California
    Posts
    1,263
    Instead of Importing the table from PAYROLL, have you considered just linking to it. This way you don't need to import, the data will always be up-to-date.
    Just like the manual Import you right click in an empty spot; but this time select Link Tables instead of Import. From there it is just like the Import, but when you are done the new table will be linked from the other database. Any changes made either in PAYROLL, or from HRIS will be made directly to the payroll database's table.
    You won't need to ever import again, once the table is linked, HRIS has access to it.

  11. #11
    Join Date
    May 2008
    Posts
    6
    Thanks for the code, im using vb6. I've tried using the link table but i dont know how to open the link table from vb6. I have posted a thread from vbforums but nobody knows they just recommend it to import manually.

    Run Time Error '2507'

    The type isn't an installed database type or doesn't support the operation you choose.

    Is there any reference to check?
    Im using ADO ODBC

    error in this line. I have tried removing the space in the database filename but still the same error.

    .DoCmd.TransferDatabase , , "C:\Documents and Settings\Administrator\Desktop\Local LB Data.mdb", , "EmpPayData1"

    Here's my code in module
    Sub Main()
    cn.Open "Payroll"
    AutoUpdateDB
    frmSelect.Show 1
    End Sub

    I create a sub routine AutoUpdateDB the one you give me
    I want it to run before the main program is loaded so that the tables are upto date.

  12. #12
    Join Date
    May 2008
    Location
    Chicago, IL
    Posts
    64
    Take a look at the link in my signature. It includes a sample app that opens a db using code, and inserts deleted records into a 'deleted' table, so they are tracked.

    Just use sql statements to insert/delete/change records
    David CodeGuru Article: Bound Controls are Evil-VB6

    CodeGuru Reviewer
    2006 Dell CSP
    2006, 2007 & 2008 MVP Visual Basic
    If your question has been answered satisfactorily, and it has been helpful, then, please, Rate this Post!

  13. #13
    Join Date
    Aug 2004
    Location
    Orange, California
    Posts
    1,263
    1. Linking a table is a one shot deal. From Ms Access open the HIRS databse.
    Link the table using the steps I gave before.
    Once the Table is linked, you use it like any other table in HIRS, even though the data is actually in Payroll.
    As far as your code is concerned it is a table in HIRS. It also exists in Payroll, and is actually the same table, but so what.
    It is still a table available through HIRS.
    You don't have to re-link it, or import it, it is just always there, so long as nobody moves or deletes the Payroll database.

    2. Your connection object is not correct. You have not setup the Connection String Properly, and you are opening Payroll when you should be opening HIRS.
    Here is an example of an OpenDb() sub and a CloseDb() sub that I use:
    Code:
    Public db As ADODB.Connection
    
    Public Sub OpenDb()
      Dim DbFile As String
      DbFile = C:\My Documents\HIRS.mdb"
      'setup connection
      Set db = New ADODB.Connection
      db.CursorLocation = 3 'adUseClient
      db.Open "PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source=" & DbFile & ";"
      
    End Sub
    
    Public Sub CloseDb()
      On Error Resume Next
      'In case I was performing any transactions and forgot to commit it
      db.CommitTrans
      
      db.Close
      Set db = Nothing
    End Sub
    Here I am using the object variable db as a public ADODB Connection object.
    Once I Call OpenDb() my code can use db when ever it needs a connection object to my main database.
    In your case that would be HIRS.
    Call CloseDb() just before you end the program.
    FYI: I usually put this code in a module, so that it is always accessable to all parts of my application.

Similar Threads

  1. Creating a database Table From DataTable
    By christianbg in forum .NET
    Replies: 3
    Last Post: 04-12-2011, 02:35 PM
  2. Sending mail with image problem
    By vikassheelgupta in forum Java
    Replies: 0
    Last Post: 01-06-2006, 02:12 AM
  3. How would u do it (design question) ?
    By Joe in forum Database
    Replies: 17
    Last Post: 04-04-2003, 05:55 PM
  4. Table relationships in Access database design-
    By Marcus Koontz in forum VB Classic
    Replies: 2
    Last Post: 03-30-2002, 09:03 AM
  5. Adding Data to a Database Field in VB6
    By Brian Higgins in forum VB Classic
    Replies: 1
    Last Post: 11-27-2001, 12:19 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center