Hello,
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..
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.
View 8 Replies View RelatedI am new with Access and I am setting up a data base that will have 16 tables and each table with have over 3,000 customers. The reason for 16 tables is that we will be inputting information on a monthly basis for each of these customers (12 of the tables) and I also have 4 tables that will be perfomring different "tasks" for each customer (one is just to keep the notes we make for each customer, one will be to show any billing done, one is going to be a summary of the entire year along with some additioanl information not entered elsewhere and the final one is our customer information table).
The customer account number is the primary key for all of my tables.I want to know how I can add a new customer (either using query or form) at one time that will populate all the tables. Right now I have everything set up as tables but I can set up queries if need be.
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.
I am using Access 2007 on my front-end and SQL Server 2014 on the back-end. I have a table of Car Dealers and a table of contacts at the dealerships. These tables are SQL tables. The user can select a dealer and then see everyone that works at that dealership. When they look at this there is a field called Email. This is a hyperlink that they can click on to open Outlook and send an email. The table called DealerEmails is an Access table. My table layout is:
Dbo_Dealers
------------------
ID (PK)
DealerName
DealerAddress
DealerCity
DealerState
DealerZip
DealerPhone
ModifiedBy
ModifiedDate
Dbo_DealerContact
---------------------------
ID (PK)
LastName
FirstName
Postion
DealerID (FK)
ModifiedBy
ModifiedDate
DealerEmails
-----------------
ID (PK)
DealerContactID (FK)
Email
ModifiedBy
ModifiedDate
Now I'm trying to write the code to add a new contact. My code works but I need to obtain the AutoNumber from When I add a new record to the table dbo_DealerContact. My code is:
Code:
Option Compare Database
Option Explicit
Dim adoDealerContacts As New ADODB.Recordset
Dim daoDealerEmails As DAO.Recordset
Private Sub cmdSave_Click()
[Code] ....
I tried to add Me.Dirty=False, but this still returned a value of 0 into my variable intDealerContactID.
I also tried moving intDealerContactID = .Fields("ID").Value outside of the With block.
I'm aware that there is a command in SQL @@Identity. But I'm unsure how to use it in this context.
Is there a way to get the primary key from dbo_DealerContacts so I can insert that into my Emails table?
hi, i am new to access and struggling so any help would be great...
i have a db with 3 tables. students contains a Primary Key ('studentid') and then 'firstname' and 'lastname' fields. The two other tables (contactdetails, coursedetails) have 'studentid' as a Primary Key and then they each have other fields.
I set up relationships between the tables (1 to 1) but i would now like to create a form that will allow me to enter data to all three tables at the same time. (this would be ideal as it keeps the data normalised) if this isn't possible i would like to create a form that adds the 'studentid' record to each of the tables.
let me know if you need anymore details but if someone can point me in the right direction it would be cool.
cheers
I am new to Access here, and looking to start up a database that will contain data for thousands of footballers.
I have on one table ("Directory"), all 5000+ players, with nationalities, names etc, and on another ("Flags"), I have all nations of the world and their respective flags (as an attachment).
How do I add the flag of a players nation into the "Directory" table for each record? Is there a quick way? I've tried "Lookup" but it won't let me do it for images, I've tried "Relationships" but without success.....surely I don't have to go through the "add attachment" process for all 5000 records?
I have three tables setup in a 1 to 1 relationship using a Autonumber field as their primary keys. I created the seperate tables to group related data together and reduce the tables sizes in general. So think of it as one main table and two tables of extended data.
I need to be able to add a record to table 1 and have it use the same autonumber to reserve that row for data in tables 2 & 3 so that my relationships stay in sync. I want to update the data in tables 2 & 3 later when that information is available.
I have been breaking the database testing some things out like building a form from a select query using all three tables. When I add the record to table 1 nothing is added to the other two. Things get messy on subsequent attempts to add data to any of the tables.
I have avoided this problems in other tables by not using the autonumber datatype, however, I don't have a good unique key that can be manually entered and kept accurate, so autonumber solves that problem in this case.
My VB is very rusty but I'm thinking there should be a way, before update, to capture the autonumber being used for the new record and write it to the other tables. I'm thinking that would be one solution, but I can't seem to get started on that code. Any help or examples would be great.
Thanks,
Jim
OK I'm totally lost again.
What I want to do is;
Use one form to add records into several tables.
I have seperated my data into seperate tables and set up one to many relationships.
tblincident
--------------
Key#
Report#
Time
Date
Location
tblPerson
----------------
Key#
Last Name
First Name
Middle Name
Address
etc.
tblItem
----------------
Key#
Make
Model
Serial#
etc.
What i want to do is use one form to enter all this data into the different tables.
There can only be one record in tblIncident that can match multiple records in the other tables.
i.e. for each record in tblIncident, I want to be able to have the capability to have as many persons related to that incident as I need.
I was thinking about setting up a tabctl for the data that has to be entered for tblIncident and then change the property setting for it to go away and then have the next tabctl appear in it's place for entering data into the next table. The problem I am having is that apparently I can only have one table as the control source for the form.
So next I tried using subforms, but that isn't working either. I can't figure out how to make the subform appear in a specific place on my form, sized in specific dimensions I want where I want.
I am just starting to learn about this relationships stuff and it's kind of difficult. How do you make it where you can do this from one form? Do I need to make a query of some sort?
Any help is appreciated.
I have a main table with subject id and other fields. I also have 16 other tables which have questionnaire information at 17 different timepoints. In those tables, the subject id is the only connected field between the main table and 17 different years. When I add a new record (by subject id) on the main table, I want it to add that same subject id to the 17 other tables because that subject id will eventually get all 17 years worth of data. But even using subforms, it will not add a new subject to the other tables unless I add some sort of information such as (date received) into the subform.
View 6 Replies View RelatedI want to make a table where in I have to input atleast 10 numbers in one reference ID which there is a start time and end time.
A form with inputing one reference ID and then adding multiple numbers at once and saving the data with a start time and end time. Is it possible to capture the time for that specific reference ID?
How to do it or a sample tracker on measuring it.
I am new to access but I am familiar with SQL databases. I have two tables: one for project data and another for project leader data. These tables are linked by a third table that relates projectIDs to leaderIDs. I am currently working on making a form that will go through each project and display the people involved in each project in a subform. I pulling the data from the the leader table that matches the the projectID with a query.
I would like to be able to update the information in this subform directly (which I can currently do) and be able to add new project leaders to a project. What is the best way to add the new leader to this? If a person is already in the database how do I add them with the form without re-adding new information to the table?
I am a new MS Access user. I am having difficulty adding columns to a pre-existing database. Is there a simple way to do this?
View 4 Replies View RelatedHello!
It's been a while since I've asked a question here, but I can't seem to figure this problem out.
I am setting up a form using data from 4 different tables, all related, and I can't figure out how to add fields that I didn't initially add during the wizard set-up. I used the wizard because it's easier for me to do that and then go in and make the changes I want to make.
When I get data from just one table, all of the available fields from that table are in the "Field List" no matter what fields I chose to include in the wizard process. That's good. But when I get data from more than one table, only the fields I initially chose in the Wizard process are showing up in the field list. I can't figure out how to add a new and different field from one of those tables.
The way I "solved" this problem the first time was to start over, creating my form from scratch--but now that I've done a lot of work, it just occurred to me that I will need to add 2 more fields. (I actually don't even have those fields in a table yet, but I will add them later.) So, this will be an ongoing problem. Since this form will get much use, by several people, I don't want to have to re-create it every time I want to change something.
So, is this possible? How can I add another field?
Also, while I am at it... A combo box was working beautifully to look up specific records when I was gathering information from only one table. It isn't working anymore.
Thanks in advance!
-Siena
I am creating a table in access 2010 for my consumable and bench stock report. I made a 12 fields which I name it the month of the year and another 1 field to add the total disburse materials in one whole year. I did this formula to add the 12 fields
[Jan]+[Feb]+[Mar]+[Apr]+[May]+[Jun]+[Jul]+[Aug]+[Sept]+[Oct]+[Nov]+[Dec].
But the problem is its just adding the complete consecutive months that I disburse and the row with blank section the total disburse doesn't show on the total disburse for the whole year. I try to use the code =Nz([Total Disburse],0) but it shows on the screen i cannot be used in calculated column.
Supposing you have 4 Tables
1.)User
2.)Admin
3.)Teacher
4.)Pupil
Now the "User" table has the ff. fields: ID(auto num),FName,MName,LName,User,Pass,Type.
Now what I want to do is, if I add a new a record in the table "User" and if the record has the data "Admin" in the field "Type" then the record should also be saved in the table "Admin" but if the data is "Teacher" or "Pupil" then it should be also saved in their respective tables.
Question is: Is that possible? If yes, then how??
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
OpenForm = true
end function
Thank You.
Hi Folks,
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.
Am I missing something obvious ?
Cheers
Karen
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?
Any help would be appreciated.
Thanks in advance
Sean Summers
Makonga Inc
Access 2003
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 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.
Does anyone have any suggestions?
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.
Thanks :)
Mikey
Hey Access people...
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.
Can anybody make a recommendation here??
Many Thanks!!
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.
Hi All
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.
Thanks for any help in advance.
Taigum