I inherited an interesting problem. I have 3700 tiny MDB files all of which contain the same table (same name, field structure, etc.), but different data. Each database has a slightly different name. It's the result of XML data mining.
I need to combine these 3700 tables from these different databases into one table in one database.
I can't see any way to automate this and as it's a one-time only project I don't know whether it's worth trying to automate it or if I should roll up my sleeves and just start apending tables.
I have started working with a database/s that was not set up by me and I am having problems appending tables from different databases.
Overview - There was one original database set up to collect clinical data with a 'mainTBL' (health data) and a 'subTBL' (medications data) linked within the 'mainTBL'. Rather than having one continuous database the data entry person has copied the database and used a copied version for each month (where the records were deleted so new records could be entered) - so I have six databases for six months of data entry and I need to make it into one big database.
There is an ID field in the 'mainTBL' that is an autonumber (and primary key) and this has a relationship with an ID field in the 'subTBL'.
The problem - The autonumbers in each monthly database are not unique across the six different databases (Eg the autonumber in both february and march starts at 74 so there is duplicate ID's in february/march). I have to append all the monthly databases (including the 'subTBL's) and then start a new autonumber so they are all unique and new data can then be entered. I am having trouble appending because of the duplicates and the relationship between the ID fields - what is the best way around this.
With referential integrity enforced, is it possible to use append query to append to multiple tables based on only one parameter that applies to only one table. If so, please enlighten me with a detailed explanation.
I try to call one database forms and tables into another database without creating tables and forms.
Database where (target) i need to open forms and tables: insurance.mdb (In the same database I click on TOOLS - REFERENCES and add accountreceivable.mdb as i need all forms and table from that database)
Database from where (source) i need forms and tables accountreceivable.mdb
I try to call forms and tables from accountreceivable.mdb into insurance.mdb. Do i use the below function in insurance database to open tables and forms from 2nd database. If i do where should i write this function i mean in the module of insurance database or in the module of accountreceivable database. How to i call that function
Function OpenForm(byref vformname as string) as boolean docmd.open vformname,,,,,acdialog
Hoping someone may be able to point me in the right direction.
I've got several databases used for different tasks, in which I just added an hyperlink to them from a front end database. All my team could access any of the data bases at once. It was using the WINNT/MS OFFICE mdw file.
Since my last question, I've now managed to set up a MS Workgroup Administrator file, which is on a shared drive. Everyone has joined this workgroup, and when people logon it now greets them with their name and records their name against any tasks. Thanks to everyone for their help with setting this up.
But since I created this new workgroup, we can't access more than one database at at time. It comes up saying 'mdw file is in use'. However, one person in the office can. I've looked at all the setups for individuals, and can't see any difference between ours & this one individual.
Ok, this is only a very general question, but I'm hoping somebody here can give me some general advice.
Basically, I have three separate database which I've written for my company. They were all written at different times, hence the fact there are three and not one that does three things. The first is used to raise, edit, delete, and track RMA records for returned merchandise. The second is used to raise, edit, and track Training data for people who have undergone my training course. The third is my main database that contains all of my clients' details, plus a log of all calls taken from them. This is the largest and most complex of the databases and has some fairly advanced relationships setup to enabled hundreds of call logs to be linked to a single record of necessary.
Now all three databases have been written using the same kind of structure. They all use popup modal forms to prevent malicious/accidental damage to the database by the staff who use them. They are all locked down and Access cannot be broken into whilst they are running. they all also have their own primary keys.
All these databases work just fine, but I'm now a little tired of each staff member having three shortcuts on their desktops, depending on which database they need to use. what I'd really like to do is somehow merge all three of these databases into one single database, and alter the switchboard accordingly to allow users to access any of the three parts of it.
The problems - as I see them - are: (a) that my main database uses a lot of relationship links to maintain certain records, whilst the other two use none, and, (b) that each database, having been written separately, has records with their own ID keys and autonumbers.
I did toy with the idea of having some kind of function on my main switchboard that would allow me to close the current database being used and open another, all without closing Access, but I haven't been too successful in my tests...
...so, my question is, does anybody have any bright ideas as to how I can go about merging these three, or, at very least, centralising access to all three?
I'm hoping someone out there can help me out with a problem. I work for a company that creates websites for car dealerships. The process involves me inputing data to a standalone program which creates a database for each dealer. When I upload the information, the program I use creates an inventory.mdb which is accessed through the asp pages I have. examples of finished product are: http://maconsales.com/freedom/inventory.asp http://maconsales.com/freedomnew/inventory.asp
We currently host about 13 dealers. What my boss is wanting me to do, and I'm having problems doing, is making it possible for someone online to visit our main site and view all inventory from all dealers without having to leave our site to look at each individual inventory. And I would like to do it without having to manually create a central database with all the inventory we have, about 1400 vehicles.
Is what I'm trying even possible? or am I looking at a long night of data entry?
I´m trying to improve my appl. but I´m stuck trying to figure out how to append multiple rows on a table using the Append query.
My problem comes since I have 4 tables with the following fields: 1) Orders OrderID (number) CustomerID (number) Date (date) etc... 2) OrderDetails OrderDetailID (number) OrderID (number) ProductID (number) QuantitySold UnitPrice etc... 3) TmpOrders OrderTmpID (Autonumber) CustomerID (number) Date etc... 4) TmpOrderDetails OrderDetailTmpID (Autonumber) OrderID (number) ProductID QuantitySold etc...
The problem comes since the Tmp tables are used just to record temporarly the information before the transaction is completed. Whe the salesman at the desk finish the sale, a command button is presseed and an append query runs to transfer the data from Tmp tables to the Definitive tables. Another query (Delete) is excuuted inmediately after the append qry. and it deletes the information recorded on Tmp tables.
I need to reset the autonumber or create a field that records the line number so I can add up to the Maximum OrderDetailID found on the definitive table, but the problem is how to create the "controlled" autonumber.
I've inherited an Access Application which has (1) a number of linked tables that are used to seed the underlying SQL Server database.
This system has been set up and is working in Development. My task is to move it to UAT but with regards to point (1) above I can't see any alternative but to copy the Access Database, create a new System DSN and re-import the External Data from the new DSN. What I'd like to do is use the same Access Database to point towards DEV or UAT or PRD. Is this possible to do this or am I stuck with maintaining 3 different Access Databases ?
I am a developer with many years of experience but my Access knowledge is limited so any pointers would be helpful.
I have around 10 mdbs and in each I have 4 tables, I want to add all the tables from 10 mdb to one mdb, finally I will have 4 toal tables, right now I am using a appendquery to add tables. Is there way using VBA I can do it on the form and with one click of a button I will be able to add. Thanks for any info..
Hi all, i hope this is posted in the right place first of all :) apologies if not..
I have about 8 individual databases which will be used simultaneously by users. I wish to be able to have full control over these databases from a 'master database' including being able to delete the data in there (possibly import fresh data but not 100% needed) and export data to be analysed in Excel etc I figure this can be done with access but if i'm on the totally wrong track i would be appreciative if someone could give me a push in the right direction..
If any more infomation is needed then let me know.
Does anyone know if it is better as a general rule (server performance wise anyway) to connect to a single database with many tables or multiple databases with fewer tables??
In this case I have a small calendar database and large navigation/metadata database -- both are MS Access & connected to the same ASP page.
I have one Master database and multiple child databases in a folder. Each of these databases (both Parent and Child) have identical table called "source". I need a VBA code which will consolidate tables of child database into the Parent database table.
I maintain a simple Access database which about 30 other users have open every day and use it solely as a reference book. It is simply a large reference 'toolbox' with one form and many many sub reports in the form with links to refference guides for my users. Very often I need to add new reports and just new content overall to the database. My current process is to
1) make a copy 2) modify the copy with all my new content 3) Send and email with the file attached telling all 30 users to delete their old database and use this updated version.
Of course we have many issues including:
1) The files are very large in Lotus notes (is there a way to make these files a lot smaller) 2) Seems like a waste to delete and replace an entire database when only 1 or 2 minor links need to be changed every time. Is there a macro or some code I can create that would act as a patch where the 30 users can run the code and it will change hyperlink paths that I needed changed, report content or anything that needs changing.
gets around having all my users copy and replacing their database toolbox on their desktop. We all work out of a shared network drive so I can have them use the database together in a shared drive but its litterally impossible to have every hand out of the database all at once so I can go in and update, it just does not happen so if there is a work around for getting all users out of the DB, it could avoid having to do anything else.
I'm scanning bar codes into the record search box, most of the time it works as it should but occasionally I get multiple "cannot open any more databases" errors. It still finds the product record but the error messages are super annoying. I am sure to clear the memory on the scanner each time before I scan in a new barcode but it doesn't seem to matter. Also sure to only have one or 2 forms open at a time, that doesn't seem to matter either. Most of the time I don't get the errors but when I have to click through like 10+ error messages (all identical). I only have one database open so I'm not sure what it even means. Also I have tried this in 2 different forms and it's the same for both forms. Seems like I get the errors in my initial scan and then after clearing them it seems to work without errors.
We have a case where there is an append query in one database that is is updating another database table. We have moved all the tables to MS-SQL (2008) using the SSMA tool. We were wondering if it would be possible to create a new linked table in the DB where the append query is. Of course we cannot use the SSMA tool again since the table already exists on the MS-SQL server.
Normally when I try to make linked tables from Access, it wants to create a ODBC connection which we are not using.
I have two tables I want to update...One table is a lookup table (Dates) and the other holds most of the information (History). I then have a query that finds the information i need that will be appended to both tables. One field in the query needs to update the dates in the lookup table "dates" and then the history need to get all related info.
Example:
Before Query - (In Date Table) (In history table) 1/2/06 Jane Doe $10 1/3/06 John Doe $40
After Query - (In Date Table) (In history table) 1/2/06 Jane Doe $10 1/3/06 John Doe $40 1/4/06 Jeff Doe $50 <---- How do I add this info, to both tables
I Have a situation where a client will want to pay some lump sum amounts of money off a loan and it may be something like $100 per month for 10 months. I have created a table for lump sums payments into which this info would be entered but I need to create an individual payment for the period of time designated ie 10 individual records of $100 each starting in a month and incrementing the month.
I see it being a append query but do not know how to make the append query create 10 instances of the one record based on a field value. I also do not know how to make the Month increment by one for each record ie Mar05,Apr05,May05 etc.
If this is possible any suggestions would be appreciated.
I have Linked a few ODBC Databases to my database in the Tables objects. How do I make them usable for queries or as record source for my form? I tried to create a query but the ODBC databases do not show up in the Table options.
I have 192 databases that I need to redact certain information in certain columns. Generally, I'm looking for field names like name, first name, last name, address, address2, shipping address, mailing address, and phone numbers. Not sure how to get this information without going into every database and every local table inside the databases. Is there a way to get this information programmatically and just access the databases and table that I need to redact info in?
Hi all Can someone help me with an Append query problem I am having. The end result I need is a Work sheet that has a Customers details, Plant details, Work required and a Subcontractor name. I have a search function that I use to select either a Customer name or Plant ID. Once I have selected the Customer name/Plant ID, I use a button to append the Customer Name and the Plant Id to a Plant_History table. This table uses an Autonumber field to create a unique identifier for each entry which I am using as the Work Sheet number. I have then added coding to open a form which accesses the Plant_History table directly so I can enter the work required and the contractor to carry out the work. My problem is that the Customer and/or Plant will have multiple entries in the Plant_History table. Currently, when I select the button to append the details to the Plant_History table, if the Plant ID is already in the table, the number of records is doubled in the Plant_History table (eg if the record has previously been entered 4 times, 8 records will be appended to the table). I am not sure why this happens, if someone could point out the fault in my work, it would be much appreciated. Regards Craig
I have a database with a number of linked tables that are linked to tables in different databases (not a back-end).for example, I have table1 that is linked to table1 in K:databasedb1.mdb.table2 linked to table2 in S:datadata.mdb.and so on...
However, recently we have moved all our databases to a new location.
K:databasedb1.mdb is now residing in O:masterdatabase and S:datadata.mdb is now residing in O:masterdata and so on...
I'm now in charge of relinking all those tables to point to the new location.I would do this in linked table manager one by one but we have 100s of tables linked to multiple different databases in different location.is there a way to create a VBA code that will automatically do this re-linking process?
so, 1. find unlinkable tables 2. search its new location under O:master 3. re-link it to the new location
Database names and tables names have not been changed. Just the location of databases.
there are many threads about Table Linking but i coud not find one that answers my specific questions.
1. Can i either prevent database from linking to my tables? (or give permissins ) 2. If not, can i make the table read only when linking? 3. Is there a way to confirm what databases are linked to my tables?
I am setting up VBA to run multiple append queries in consecutive order. The append queries are supposed to pull a four digit number off of a form that will allow them to know which data to append. I want the number to be stored as text to correspond with the tables I am working with, but I am having trouble getting the form to work.
Here is how I would like it to work:
I enter my 4 digits in the text box on the form. Say "1305" for May 2013. I want to then run my vba (my queries update using the forms!txtupdatequery!textbox I have put in the criteria of the queries) and all of them run.
I enter my date (as a number but I want it to be text) and the form gives me a #name? error.
I have a dilema on my hands as I can't seem to figure out the best way to accomplish this. I created a database to handle all of our letters for my department in the company. The problem I am having is that we have 26 people who could possible access the database at the same time. The letters are created using a form. (not a problem with multi-users) When someone wants to print the letter the do a search either by "Letter Date", "Provider Name", "Provider Number", or "Group Name". I set it up so either one of these search queries append data into one table called "Letter Report Information" all 38 letters get there data from this 1 table. The problem I am having is that if John and Mary try to pull different/same letters at the same time or while one is viewing their letters the data is getting pushed onto the others Letter.
Example:
Mary goes to print her Welcome letter John also goes to print his Denial letter at the same time
When John's letters show up.. the Denial letter shows up with his data, but also with Mary's Welcome letter data.
What can I do or what kind of query can i use to avoid this problem as it is becoming a very prominent issue? ::Please Help::
