how to Insert data into external db

method · April 28th, 2005, 02:29 PM

Hi experts. I want to learn how i can insert data in to external db using vba.
For example , i want insert table names in to the external db.
(A command button in a form )I be happy if some one show me how. Thanks

Steven · May 2nd, 2005, 07:15 PM

Well, it all depends on exactly how you want to do it, and what you're comfortable with.
If you're not that comfortable with Access or programming at all, I'd suggest using Linked Tables.
If you want to do something fancy, and you don't mind programming, it depends on a) what version of access you're using, and b) what method (most likely ADO or DAO) you'd prefer.

But using linked tables would be the easiest - then you can just treat the table like it's local.

Steven

I am a loud man with a very large hat. This means I am in charge

method · May 3rd, 2005, 07:06 AM

Thank u for u reply. Could u show me an example that inserts data to external mdb file using vba 2000.Thanks

BrianWren · May 3rd, 2005, 01:14 PM

Method,

What application is the host for the VBA you are using?
I get the impression that you want to moify (for instance) an .MDB through Word, or Excel.

Please explain your situation a little further.

method · May 3rd, 2005, 04:21 PM

Many thanks to your reply. well i try to make an access 2000 vba application that has button on the form.And once i click on the button it goes and collects some information about the access db structure such as number of tables and data type ... and then writes writes them to another .mdb file that it has has the approperate tables . My difficulty is how i can wirte those information to the external .mdb file. Thanks

BrianWren · May 4th, 2005, 10:59 AM

Though most often a database object is used to refer to the database in which the code resides, there nonetheless is functionality to open a different database.

Having done that, you can use that database object to refer to all of the objects in the external database with exactly the same syntax you would use to reference local objects if the database object referred to the local database. You can open recordsets on the tables and queries in the other db, create and delete tables and such.

Code:

    Set db = DBEngine(0).OpenDatabase("C:\Path\DB.mdb", _
                                      BoolExclusive, _
                                      boolReadOnly)

where BoolExclusive and boolReadOnly are variables of your own (not built-in constants) reflecting the way in which you want to open the external database.