Are there automated methods for data entry in MS Access?

[Francis]

I used Access years ago and am just reacquainting myself with it now. I have data that I want to input to Access but I wanted to do it by an automated means.

Has anyone used a macro program to do this like Macro Scheduler or Winautomation and how reliable is it? Is there another better way to do this.

Thanks

[ArviLaanemets]

Make your database on SQL Server;
Create Stored Procedures, which read data from outer sources and/or make all needed calculations;
Create a Job which runs stored procedures on shedule;
In case there is a need for direct user input and/or adjusting settings for database, and/or reporting, create an Access Front End application for this (in case of reporting, you also can use Excel - p.e. when you need graphs as reports).

In lesser extent, it is also possible to make same using access only - instead of SQL Server Jobs you have to use Scheduled Tasks on your computer, or on server housing your Access Back End database. Scheduled Task opens Access Back End without activating it, runs a set of queries and/or procedures, and then closes the Back End.

[orange]

Tell us a little more about your "automated means". Importing from txt file or csv?

[Francis]

thank you both for your replies.

What I am trying to do is create a simple database for car owners. I have 2 related tables. The 1st is the car owners table, which has their name, city and state. The second related table is the cars table, which has the make, model and year of the car. It is a one to many relationship, as one owner can have many cars.

The car owners table has a primary automatically indexing field, the next field is their 1 st name, the next field is their last name, the next field is their city and the last field is their state. The related table of cars has an primary automatically indexing field, a field for make, a field for model, a field for year and a foreign key field which is joined to the primary key field of the car owners table.

I receive the data for this database in the form of email attachments in a CSV file that is sent from time to time. The email attachment I get contains the information for one and only one car owner at a time in the one email. The format is always the same with each value separated by a comma as follows: 1st value is the car owners 1st name, the second value is the car owners last name, the 3rd value is their city, the next value is their state, the next value is the car make, the next value is the car model, the next value the car year, the next value is the car make, if any, that they own the next value is the car model, the next value is the car year, etc. and it repeats for as many cars that they own. No car owner has less than one car but no more than 10 cars.

How can I automate this so the data automatically populates my MS Access 2016 database when I go into my outlook email account? I have one email account and address just for this purpose.

Thank you so much for any help with this

[June7]

Need to somehow connect to the Outlook email and parse the text string.

Access can set link to Outlook folder but if the email account is not your default email, not sure that would work.

VBA can use Outlook automation code to open Outlook to read emails, again you need to pull from an account that is not assigned to you but is a shared account.

Most examples I find involve Excel but can certainly be adapted to Access. Such as http://www.gmayor.com/extract_data_from_email.htm

[Francis]

ok yes, June7, extracting the data from the email is a job in itself. I have seen software available for that purpose, G-lock and Mailparser seem to specialize in that task so I will try those 1st to see if the work.

My real issue is how to put the data into the database without having to manually type it in.

In my Access database I have a sub form which is the Cars table inserted into my main form, Owners table. If I enter data now by typing it in, I open this form and enter a new owners name, city and state. I then go to the sub form on the same form, and enter the make of the car, the model and the year. If I already have a make the same it selects that from the drop down list, same for the model and year. If I do not have that particular car in the Cars table, I type it in and it is stored as an entry for the current car owner and of course for future data entries when needed. I keep entering the information for the cars that an owner owns until I have completed entering all the cars they own.

To automate this task is were I am stuck. I pressume I need to use a macro, but would this be in Access itself, or an outside one like Macro Scheduler or do it need VBA what is the simplest and most reliable approach to this.

I was just hoping someone here has done this before and knows the better way to do it.

Thanks again

[orange]

See this thread where June gave a lot of info. You might be able to contact user MTSPEER to find out what the project status is.

[Francis]

Thank you Orange I read those references several times over. Right now I am not concerned about how to get the data from an email attachment, I believe I can do that with 3rd party software.

The only question I am focused on is how can I automate entering the data into my Access database without manually typing it in?

I can put the the data in Excel if need be but how do I then take that data from a flat file, which Excel is and connect the data so that it works in an MS Access relational database that I have set up?

When I enter the data by manually keying it in, I 1st go to the form that has the Owners table with the sub form in it Cars table, when I start to enter the owners data, it automatically increments the primary key for the Owners table, then when I have finished entering the owners data, I tab to the Cars table and when I start to enter the data for the make, it automatically increments the primary key field in the Cars table, I then enter the rest of the data for the car, like the model and then the year, and then automatically it enters the foreign key value that relates to the primary key in the Owners table, that is how the database keeps the data related to each other. But this process only seems to happen when I enter the data manually using the keyboard.

My one and only question is there a way to enter this data without manually typing it in.

Ok thanks again and any help or suggestions.

[orange]

I suggest you do some research (google can be your friend).

see this as a start https://www.simonsezit.com/article/i...o-access-2010/

[Francis]

Ok thanks. I read the link.

I don't think you understand what I am asking.

I want to import the data yes, I get how to do that, but more importantly - I am asking how to set the relationships between the data - that I import so that they relate to each other.

For example I have a csv file or text file that has the following:

Fred,Jones,Atlanta,Georgia,Ford,Escort,2008,Dodge, Dart,1973,Honda,Civic,2000

I would like this data to be inputted to my Access database so when I look at my form that has the Owners table with the Cars table sub form in it, I see Fred Jones who lives in Atlanta Georgia, owns a Ford Escort 2008, a Dodge Dart 1973 and a Honda Civic 2000.

If I were to do a report of all the car owners and the cars they own I want to see that Fred Jones who lives in Atlanta Georgia, owns a Ford Escort 2008, a Dodge Dart 1973 and a Honda Civic 2000.

That is what I am trying to accomplish.

One way I can do this is to import the data and map the fields to match my database which is fine, but then I have to open my form and select the owner Fred Jones of Atlanta Georgia and then tab to the cars subform and then select each car from the drop down list.

The other way is to do it is to open my form and type in the data using the keyboard. Either way requires manual input.

I am asking as plainly as I can, do you know of a way to automate this so that I do not have to type it in and the data be related to each other and not data sitting in a table that is not joined or related to other data, like in my case a car owner to the cars that they own.

Ok

[moke123]

A lot will depend on the consistency of your data. Will the first 4 sections always be Firstname,LastName,City,State, followed by Make,Model,year?
Will there ever be a need to Append a new record to an existing Fred Jones with another car?

[Francis]

Yes to your questions, for now the data only arrives in the one standard format.

I presume that if I did need to modify what cars people own, I could do it in the same way as I entered the data in the 1st place. The format of the attachments might change, it might mean that cars that are removed, would have a (-) sign in front of them.

I was thinking maybe this should be done in a MySql database, but I don't know as I only know Access, it would be a bit of a learning curve but maybe worth it if there was more functionality and I could write a script or if there were already made scripts for this.

I don't know that is why I am asking.

Ok thanks for your help.

[ArviLaanemets]

At first, there is serious flaw in mail attachment info (and in your Cars table too). For car you have make, model and year. But those 3 characteristics don't identify a car! Even the same owner can have several cars with all 3 characteristics same! You need something like VIN Code there too! And this in mail attachment too, not only in your database - otherwise you can never be sure you connect the attachment info with right car in your database.

The second. The automation must be started not in Access, but in Outlook. Do e-mails have something Outlook can recognize them, p.e. specific subject, or specific sender address(es).

When yes, then outlook can save them into specific folder. From there on, Outlook or some 3r-d party program automation has to kick in (maybe Access is also capable for this, but I'm not sure). Periodically some script must be started, which checks the outlook folder, when the folder is not empty, processes the e-mails in folder, copies attachment from every e-mail into some folder in your network, and moves the processed e-mail into another folder in Outlook.

The next step. The same script as in previous step continues, or a new one is started (p.e. a VBA script called from Window's Sheduled Task). All csv files in network folder must be processed, and the info is car-wise saved into separate table in your database, p.e. dummy_Data: MailCounter, FirstName, LastName, City, State, VIN, Make, Model, ProdYear. When a csv-file is processed, it is moved into another folder in your network. When all csv-files are processed, the first network folder will be empty.

(MailCounter is a mail processing order number in script. It is needed, when you have p.e. several John Smith's in same state and city in your database. It allows for next script to bind the owner of new car to certain owner in your database, when he/she had another cars there before.)

Now Access steps in (when you din't use it on previous steps). You can start a script from scheduled task which calls a procedure in Access, or simply you can use an OnOpen event of Access application for it. The procedure/event updates car info in your cars table when the car is present in your Cars table and in dummy_Data table, then adds new cars (car is in dummy_Data table, but is not in Cars table), and as last marks inactive Cars table rows where car is not found in dummy_Data table. After that all data is cleared from dummy_Data table.

[Francis]

yes you are right there needs to be unique identifying data in both the Owners table and the Cars table, there is but I didn't include it here as I wanted to keep the example as simple as possible, to focus on the principle of what I am doing.

I am not worried about getting the data from an attachment. Let us just say I have all the data in csv files on my computer.

I still don't think my question has been answered, at least I don't know any better right now what to do.

Thanks anyway, it is appreciated.

[CJ_London]

if this is your data

Fred,Jones,Atlanta,Georgia,Ford,Escort,2008,Dodge, Dart,1973,Honda,Civic,2000

Implies Fred Jones has 3 vehicles, presumably someone else may have any number of vehicles. And all on a single line

Acknowledging the Avril's comments, the data is insufficient to identify a specific model, or that a person may have two cars with the same 'spec', as a minimum the principle of converting this to normalised data is as follows

Assumption 3 tables

tblPersons
PersonPK autonumber
firstname
lastname
city
state

tblVehicles
VehiclePK
Make
Model
ManufactureYear

tblOwnership
OwnershipPK
PersonFK
VehicleFK


1. link to the data or import to a temporary table
2. from that temp table use a query to append the persons detail (Fred,Jones,Atlanta,Georgia) to a persons table - excluding those records where you already have a Fred Jones of Atlanta Georgia
3. from that temp table use a query to append the first vehicle detail to the vehicle table (Ford,Escort,2008) - excluding those records where you already have Ford,Escort,2008
4. repeat 3 for other vehicle columns as many times as required and ignoring those rows where the data is blank for those columns
5. from that temp table use a query to append the PersonPK and first vehiclePK to the ownership table the query will need to look up the PersonPK from tblPersons and VehiclePK from tblVehicles using the values in the temp table
6. repeat 4 for the other vehicle columns as many times as required and ignoring those rows where the data is blank for those columns

As you can see, it involves a larger number of queries

Also this principle needs to be much more sophisticated in real life - what if you already have Fred Jones in your system?, What if you have more than one Fred Jones in Atlanta, Georgia? What if Fred owns two Ford Escorts from 2008? what if he sells the Honda Civic? etc. These also need to be taken account of in some way, but at the moment, you have insufficient data