Results 1 to 9 of 9
  1. #1
    Join Date
    Feb 2003
    Location
    Toronto, Canada
    Posts
    18

    Question Unanswered: Using VBA in Access2000 to get data from another Access db

    Hello,

    I need some help. I am writing an App using Access2000 and have to get certain info from another Access db (in another location). Can anyone please point me in the right direction ?
    Thanks

  2. #2
    Join Date
    Mar 2004
    Posts
    118
    Why dont you just link the relevant tables from your other database into this one?

    File -> Get External Data -> Link Tables

    Then select your other database.

    Name them however you want and use them as normal tables in your code...

  3. #3
    Join Date
    Feb 2003
    Location
    Toronto, Canada
    Posts
    18
    That is an idea. I was thinking of using VBA. I saw it used that way a while ago.

  4. #4
    Join Date
    Mar 2004
    Posts
    118
    you can use a VBA link, but thats thesame thing...

    If you link them in access your keeping you code clean and less complicated.

  5. #5
    Join Date
    Feb 2003
    Location
    Toronto, Canada
    Posts
    18
    I don't know how to do that. Can you please give an example ?
    Thanx

  6. #6
    Join Date
    Mar 2004
    Posts
    118
    DoCmd.TransferDatabase acImport, "Microsoft Access (INSERT TYPE OF DB)", "DB NAME", acTable(INSERT TYPE OF SOURCE), "SOURCE", "DESTINATION", False(FALSE = EVERYTHING TRUE = STRUCTURE ONLY)


    Here you go.... All the stuff in caps is my comments.


    In the future, you can make a msaccess macro and convert it to vb code yourself

  7. #7
    Join Date
    Mar 2004
    Location
    www.scirocco.ca
    Posts
    346
    Here is some code to connect to an Access database, reterive data from Table1, close the recordset and close the connection using VBA with ADO:

    Dim cn as ADODB.Connection
    Dim rs as ADODB.Recordset

    Set cn = new ADODB.Connection

    cn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;User ID=Admin;Data Source=C:\Test.mdb;Persist Security Info=False"

    cn.open

    set rs = new ADODB.recordset

    rs.ActiveConnection = cn

    rs.Open "SELECT * FROM Table1"

    rs.Close

    cn.Close

    Set rs=Nothing
    Set cn = Nothing
    http://www.scirocco.ca/images/banner...occobanner.gif

    Download for FREE the ADO/DAO Data Controls that makes life EASIER developing database applications in: VB, FoxPro, Access, VC++, .NET etc... Navigate, Add New, Delete, Update, Search, Undo and Save your changes. Supports Disconnected Recordsets and Transactions!

    Or try our Ask An Expert service to answer any of your questions!

  8. #8
    Join Date
    Feb 2003
    Location
    Toronto, Canada
    Posts
    18
    Thanks. I'll give it a try.

  9. #9
    Join Date
    Mar 2004
    Location
    www.scirocco.ca
    Posts
    346
    By the way, don't forget to add a reference to the Microsoft ActiveX Data Objects (ADO) to your project.
    http://www.scirocco.ca/images/banner...occobanner.gif

    Download for FREE the ADO/DAO Data Controls that makes life EASIER developing database applications in: VB, FoxPro, Access, VC++, .NET etc... Navigate, Add New, Delete, Update, Search, Undo and Save your changes. Supports Disconnected Recordsets and Transactions!

    Or try our Ask An Expert service to answer any of your questions!

Posting Permissions

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