How to import table from another database

[lantis]

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

[Ron Weller]

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..

[lantis]

I think thats a good idea... how? Help. Pls

[Ron Weller]

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--

[Hack]

--Don't know about MySQL--

Me either, but in case someone is interested, I did find this on the MySQL site.

[lantis]

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

[lantis]

if u have any suggestion to make my program easier ill greatly appreciate it. Thanks again

[Ron Weller]

Here is how you could Automate Access to do the Import:

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

[Ron Weller]

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.

[Ron Weller]

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.

[lantis]

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.

[dglienna]

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

[Ron Weller]

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:

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.