none
Creating queries using external ACCDB

    Question

  • Hi guys,

    Just wondering what I should do to create a query using external ACCDB

    In VBA Code, to add data to my DB, I use

    Set db = OpenDatabase("D:\DB.accdb")

    and then normal VBA commands.

    Is there any way of creating a query using MSACCESS query wizard?

    Please, may anyone give me a light on how I should proceed to create query using external ACCDB file?

    I am not beginner on this, but it is being my first time using external DB

    Regards,

    Igor

    Wednesday, October 30, 2013 4:19 PM

All replies

  • Igor

    Why not link to the external tables? That way they behave just like local tables except you cannot make any changes to the linked tables' design.

    Otherwise, you have to write the query in VBA and it's only good while the db object is connected.


    Bill Mosca
    www.thatlldoit.com
    http://tech.groups.yahoo.com/group/MS_Access_Professionals


    Wednesday, October 30, 2013 5:50 PM
  • You are right, I could link the tables.  but the problem is, the user with full ms access (not with just runtime) can open the tables through the left frame and change or see the information in the table

    Do you have a solution for this?

    I want to prevent the user from accessing the tables through the ms access

    Thx

    IGor

    Wednesday, October 30, 2013 6:41 PM
  • Igor,

    Very strange, how to design query without access to table and data, workaround for "tabx":

    'Link table from source accdb
    'For clear example ommited
    'Check here: 
    'http://stackoverflow.com/questions/17932353/link-table-via-dao
    'Linked Table name: tmpTab
    
    Dim sql as String
    Dim qdf as DAO.QueryDef
    
    'Copy only structure:
    sql = "select * into qTab from tmpTab where 1=2"
    Currentproject.Connection.Execute sql
    
    'create tmpQuery
    sql = "SELECT * FROM qTab"
    Set qdf = CurrentDb.CreateQueryDef("tmpQuery", sql)
    
    'Open for design
    DoCmd.OpenQuery qdf.Name, acViewDesign
    

    After user confirm end work:

    Set qdf = currentdb.QueryDefs(tmpQuery)
    resultQuerySQL = Replace(qdf.SQL,"vTab","tab1")


    Michał

    Wednesday, October 30, 2013 7:26 PM
  • Michal,

    I dont want the user to have access to the table straight through the ms access

    I want it to access the tables through the forms, where it will add data to the "edit" fields and then click in a button to run the VBA code.

    That why I thought of having a separated ACCDB with password and then open it using VBA like,

    Set db = OpenDatabase("D:\DB.accdb", False, False, "MS Access;PWD=password123")

    with that I prevent the user to open the tables

    creating the ACCDE I prevent the user to access my VBA code and forms design

    Now I would like to learn how I can create queries using SQL or another way to bring information from the "DB.accdb" and fill the report

    Understand?

    Wednesday, October 30, 2013 7:55 PM
  • You can use the IN Clause to refer to a Table in an external database as per

    Microsoft Office Home article: IN Clause

    However, I think there is a restriction that you can have only 1 IN clause to connect to only one external database at a time.  Furthermore, even the article for IN Clause above says "For improved performance and ease of use, use a linked table instead of IN.".  Another point to note is that for anyone proficient with full Access version, they can find the SQL String of your Query and find out everything about the external database and the source Table you are trying to hide in the first place!  

    Another way to refer to a Table in an external database is to use the (implicit IN) database qualifier as espoused in 

    MSDN Thread: How can I copy a whole table from MS Access database to another?

       


    Van Dinh




    • Edited by Van DinhMVP Tuesday, November 05, 2013 2:48 AM Typos
    Thursday, October 31, 2013 1:51 AM
  • The users of that app are quite dumb, but you know users are the best to find things they shouldnt (lol)

    I think the best way should be taking that SQL query to the VBA code, then make accdE, so I can "hide" the password

    what do you think?

    One question

    SELECT * FROM table IN "D:\Database\DB.accdb"

    perfect, it works, but how to add password to this line?

    Thx

    Thursday, October 31, 2013 8:33 PM
  • The users of that app are quite dumb, but you know users are the best to find things they shouldnt (lol)

    I think the best way should be taking that SQL query to the VBA code, then make accdE, so I can "hide" the password

    what do you think?

    One question

    SELECT * FROM table IN "D:\Database\DB.accdb"

    perfect, it works, but how to add password to this line?

    Thx

    When I said using the SQL query inside the VBA code, I meant using it to populate a TEMP table which will be used by the report and after that clean the data in that temp table

    Do you think that's gonna work?

    Thursday, October 31, 2013 8:50 PM
  • >>..., but how to add password to this line?<<

    Not sure since I don't even use IN clause for the reasons previously mentioned.  However, I think it will be an additional argument in the inplicit IN similar to

    http://www.connectionstrings.com/access-2007/

    >>Do you think that's gonna work?<<

    It should work but INEFFICIENTLY with writing to temporary Table then reading from this temporary Table and finally deleting data in this temp Table.  IMHO, besides reading from the real Table which you need, your process adds extra steps in writing to the temp Table, reading from the Table then deleting records in the temp Table (another writing step).  Hard dish reads and writes are certainly much slower than manipulating data in the memory so the most efficient method is to minimize the hard-disk reads/writes as much as possible.

    If data security is of utmost important then you should look at some other database engine such as SQL Server where the full-feature security is implemented in the database engine.  You still can use Access Front-End accessing data stored in SQL Server database.

         


    Van Dinh



    • Edited by Van DinhMVP Tuesday, November 05, 2013 2:50 AM Typos
    Friday, November 01, 2013 10:59 AM