Page 2 of 2 FirstFirst 12
Results 16 to 24 of 24
  1. #16
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,533
    Thanks
    0
    Thanked 23 Times in 23 Posts
    I did something like this many years ago, I would generate a UNION query on the fly to access all the databases (84 in all, each with 2 million records).
    So I would tend to use a UNION query as a basis to search for the records, then use queries that use the Union query.
    I think I also linked tables on the fly too.
    Last edited by patt; 2014-08-13 at 23:40.

  2. #17
    New Lounger
    Join Date
    Jul 2008
    Posts
    17
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thanks again.

    I have built a basic search form using a Query and Button to run it for FE table. I am still having 2 issues as below

    1. I am still facing issues while importing the data from excel to FE table which then to 3 different BE tables. My Excel has 24 Fields, while DB has 27 Fields, Those 3 fields are Date & Time of Import, Username of Import & Condition status. Either I see the DB Table Structure gets overridden or no import is happening. Pls suggest.

    2. I have created a Union Query by consolidating all the Tables (both FE & BE). How should I declare the Parameter, so that I can use it for the Search form which I created ?

  3. #18
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,533
    Thanks
    0
    Thanked 23 Times in 23 Posts
    1. have you tried importing the excel spreadsheet to a non-existant table? It should bring all the fields in. If that works, why not delete the table before importing the spreadsheet.

    2. Use another query that uses the union query as it's source.

    Are you using ODBC to reference those BE tables?

  4. #19
    New Lounger
    Join Date
    Jul 2008
    Posts
    17
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thanks Patt. Not tried it yet...

    No I am not using the ODBC reference... Its just the normal Queries & forms accessing the both the FE & BE.

    Need ideas on below;
    1. Any suggestions on capturing the Date & Time for each Import along with Username for each Import?
    2. If I convert the DB to an EXE file to protect the DB from an editable form, Can the Queries, Forms, Reports, Macros & Other Access objects be added to the DB in EXE file?

  5. #20
    New Lounger
    Join Date
    Jul 2008
    Posts
    17
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Need ideas on below;
    1. I tried capturing the Username for each Import, So I tried calling the User-defined Function through table design, but User-defined functions do not appear. Pls suggest....
    2. Any suggestions on capturing the Date & Time of Import ?
    3. If I convert the DB to an EXE file to protect it from editing, Can I add new Queries, Forms, Reports, Macros & Other Access objects to the DB EXE file in the future?

  6. #21
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,533
    Thanks
    0
    Thanked 23 Times in 23 Posts
    Need ideas on below;
    1. Any suggestions on capturing the Date & Time for each Import along with Username for each Import?
    Where do you wish to capture the Date & Time for each Import along with Username for each Import?

    2. Any suggestions on capturing the Date & Time of Import ?
    Answer q1 first.

    3. If I convert the DB to an EXE file to protect it from editing, Can I add new Queries, Forms, Reports, Macros & Other Access objects to the DB EXE file in the future?
    An EXE cannot be created, however an MDE can be created, tables and queries are editable as far as I know.

  7. #22
    New Lounger
    Join Date
    Jul 2008
    Posts
    17
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hi,

    Point 1 & 2. I want the Usernames to be an Auto calculated field in a Table where Iimport. We import 28 fields from excel, while Access has 30 Fields. 2 excess fields are Username of the Person who imports & Date of Import..Help required here..

    Point 3: If I Convert Access Database (ACCDB) to EXE File to prevent users from editiing the DB, I wanted to knw whether Any Future Access objects can be addded to the DB in EXE mode created already or Should I need to create a new DB in exe mode with those enhancements ?

  8. #23
    New Lounger
    Join Date
    Jul 2008
    Posts
    17
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thanks to all. I have managed to Capture Username and Time of Import through Update SQL.

    Now I require help on the below:

    1. I am designing a Search form from Union SQL of all 4 tables (3 Backend Tables). I want only important 10 fields to display instead of all 27 fields.
    2. I want to Add & Edit records through this Search Form to the Table I wish.
    3. I also want a Check box to be displayed on the side, where I will select few records to change the fields I want, where this checkbox is mere selection and does not represent any fields in all the tables.

  9. #24
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,494
    Thanks
    3
    Thanked 42 Times in 42 Posts
    Thanks to all. I have managed to Capture Username and Time of Import through Update SQL. (Note that this could also be done by setting defaults for the 2 added fields.)

    Now I require help on the below:

    1. I am designing a Search form from Union SQL of all 4 tables (3 Backend Tables). I want only important 10 fields to display instead of all 27 fields. Using a bound form, you can choose to display any or all of the fields in a record - do you intend to display only one record at a time?
    2. I want to Add & Edit records through this Search Form to the Table I wish. In general Union queries are not editable since the result doesn't show what table the record came from. In order to edit a record in the correct table you will need to do a good deal of VBA programming, using either DAO or ADO to find the specific record in the specific table and make the desired changes.
    3. I also want a Check box to be displayed on the side, where I will select few records to change the fields I want, where this checkbox is mere selection and does not represent any fields in all the tables. If you wish to review records and set the check mark and then come back and edit just those records, you will need to add a check mark field to each of the tables involved, and you will need to be able to apply a filter to the form to find the records that need editing.

    Based you your last two questions, and the complexities associated with implementing such a solution, I would strongly suggest you consider hiring a consultant to assist you with your project.
    Wendell

Page 2 of 2 FirstFirst 12

Tags for this Thread

Posting Permissions

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