Beginner needing help

[drolan]

Hi everyone,

I am a very beginner at Access. I have done some programming in statistical packages like R and Stata and have written some basic batch scripts before.

I am currently using access as a GUI for some students to do data input for a project. I have created 83 excel files with 200 observations each and I need to put those in 83 Access files, with the same interface, form, etc... Basically the access file is ready, all I need is to input excel file 1, specify 2 variables as "Date/Time", specify one as "Yes/No", and specify values for one as only being "Found, Not Found, Not Sure."

If this were stata, I would just write a loop to go from 1 to 82 (the excel files have names that are in that order) to attach the excel, do variable changes, then save the access file.

Is there a way for me to do this in Access? Would any be kind enough to write some basic code for me to do this? I can modify it once I understand the basic structure of the language.

Any and all help would be very appreciated.



Thank you very much,
Dom

[ranman256]

no, I think you want all 83 in ONE table. Now you can query on anything at anytime.

[orange]

As ranman has alluded, why do you think you need 83 tables in Access?

[drolan]

Hi All,

The reason for the 83 tables is for:

1. Privacy. This will be data about real people. We do not want students to be exposed to more observations than they need to.

2. Efficiency. In those projects, there is always one student who goes faster than the rest and one that goes very slow. If student 1 finishes 1 file in 1 week, and student 2 finishes 1 file in 2 weeks, then student 1 will end get twice as many files to work on as student 2. This could get fixed with a query but unfortunately we would prefer to have split files for privacy as stated above.

[Micron]

I didn't read it as 83 tables, I read it as 83 separate db files

I need to put those in 83 Access files,

Your users shouldn't be able to directly access your tables anyway. I think you're making this way more difficult than you need to. If the same 200 observations are in each file

83 excel files with 200 observations each

the observations, along with supporting tables, should be in one db.

Sounds to me like you are looking for someone to write code that will copy a db 82 times, associate each one with the correct Excel file. I mean no disrespect, but you say

I can modify it [code] once I understand the basic structure of the language.

which sounds over confident to me. Perhaps that's because now I get the impression that you don't have a grasp of the basics of Access and how to compartmentalize/restrict data access. I'm fairly sure that either of us who have responded thus far could do this, but it would be a lot of work, and based on your limited experience, you wouldn't know how to fix it if/when it fails. Wondering why you think that experience in writing batch files gives you the foundation for working with the Access Object Oriented Property architecture or VBA syntax.

We could probably provide some guidance on where to research how to create a db project if you wanted to take a more normal approach. Then again if I've misunderstood the original post, ignore my objections.

[drolan]

You are very right in asserting I am a beginner at writing codes in VBA syntax. I do in fact have 83 tables in 83 different excel files. How do you mean, they should not be able to access the tables? If I import a table from excel, is there a way to make it such that just for one user can see it? The issue I am facing is that (it has been decided by the powers that be and I cannot change that) student 1 should not be able to access the work of student 2. If I load all the 83 tables into 1 db, wouldn't student 1 have access to the work of student 2? Also, wouldn't the form that each student use be linked to a single sheet/table?

My option at this point, as was stated to me by said powers that be, is that I need to manually go in, import spreadsheet 1, do some manipulation, save this as completed db file 1, rinse and repeat for all 83 files. I know that there must be a better, easier, and more straight forward way.

I would like to point out that the reason for all the restrictions on this work is that the information that will be handled are very delicate proprietary data on real people that is owned by a university and thus requires very restricted access. For instance, students that will use this data will be in a closed room with no windows, with a security checkpoint and no internet access.

[Micron]

If I import a table from excel

I meant users should not be accessing Access tables directly. IIRC, Excel added the ability to create a spreadsheet table (circa 2007). Here, I think you will find that most respondents will associate the word table with Access, not Excel, hence confusion likely will abound.

If I load all the 83 tables into 1 db, wouldn't student 1 have access to the work of student 2?

Depends on your level of knowledge with respect to preventing that, and their knowledge on how to circumvent your efforts. Then there is the desire to do so and the ability to do so while under the watchful eyes of exam monitors (or whoever we're talking about). Assuming I as a student, cannot access the individual file of another by any means, then I guess your original direction is one that you need to follow. If not, then what guarantee do you have either way?

Far be it from me to insist you put yourself in the position of creating a "normal" singular db, only to have someone thwart your implemented security. Usually, Access security is only as good as the best hacker who will use it. As far as I'm concerned, what you're asking for is not "basic" (I for one have never attempted anything like it since it's so unorthodox) regardless of how you've started this. However, I can speculate on how I might go about it.

Since I appear to have 83 xl files already, I'd use Automation in Access vba to open the msoFileDialogFolderPicker and navigate to the folder where they're all kept. After returning that location to my code, I'd step through all those files, one at a time, and create and save a new db file with (presumably) the same basic name as the xl file and append that to a singular folder location. There should not be any other files in the source folder. Then I guess I'd import the xl file as a table into Access, save it, close it, and start again after with the next one. But I don't know what you're talking about when you say you need to "specify" variables. Maybe that's something you don't automate?

Probably not as much help as you were looking for. Perhaps the upper echelon needs to open their wallets if they aren't giving you enough time to do the manual thing. Again, I believe it's doable, but never having done so, I don't know how long it would take to write from scratch. Anyone else here have anything that resembles what I described, or a better way to go about it?

[ssanfu]

If I load all the 83 tables into 1 db, wouldn't student 1 have access to the work of student 2? Also, wouldn't the form that each student use be linked to a single sheet/table?

It sounds like you are getting Excel and Access confused. Excel and Access are two different programs built on two extremely different object models.

Using Excel, how do you prevent Student 1 from opening the Excel workbook for Student 2??

For instance, students that will use this data will be in a closed room with no windows, with a security checkpoint and no internet access.

How many computers are in this closed room??
How many students are in this closed room at one time, entering info??
If more than 1, you would need a LAN (doesn't need internet access) to link each student computer to the one computer with the BE loaded on it.


In Access, you would split the dB into a front end (FE) and a back end (BE). The BE holds the data and the FE holds everything else.
In Access, you create a login form with a userID and a Password.
You use queries with a where clause to limit/filter record so that Student 1 can see only their own records.

After the forms, queries and code (if any) has been created, you could create a run time version of the FE, which compiles the FE. With the run time, Office/Access would not be required to be loaded on any computer.
The database window and toolbars can be locked down so there is no access to them.

To secure the BE, you can change the BE file extension to something other than "accdb".
You can rename a back end, say "DatabaseBE.accdb" to maybe "Bananas.xxx" or "Washington.1234", then relink the FE(s) to the BE.
If you find the BE (Bananas.xxx or Washington.1234) and try to open it, Windows asks you what program to use. Without specifically knowing that the ".xxxx" or ".1234" is an Access file, you can't see the data.

To me, this is way more secure than Excel. There are ways to "unlock" a password protected Excel workbook (in under 5 minutes... sometimes), so I don't consider that Excel is "safe".


The Excel data from the 83 workbooks could be imported into the access table (in the BE).......
Any chance you could post an example of an Excel file with a few examples?? Change the names (if any) to something like "Mickey Mouse" and make up other sensitive data.......




My $0.02 worth.......

[drolan]

Hi all,

It is likely that I did not explain my problem clearly enough.

The data are in excel, while I am using Access as the FE for data verification. Thus, I need to create 83 different Access db, each corresponding to the 83 different excel files (imported as BE tables). Every time a student completes data verification on 1 db, I give them a new one to work on. I am the only one with access to all the spreadsheets and dbs.

Not to worry however, after reviewing everyone's suggestions, and doing some digging of my own, here is what I did:

I first import and excel spreadsheet as a table in the db. Then I set the field types manual. This will be my blank slate.

Then I write a query that will delete all records in that table. I use a macro to populate the empty table in my db with data from excel spreadsheet 1. I also put the delete query in that macro.

Thus the macro deletes the data in the table (without altering the table's field names and data type, which are necessary for the FE), then it populates the empty table with spreadsheet 1, I save the db as db 1 manually. Rinse and repeat for the 83 files, which should take me very little time to accomplish.

[Micron]

Can't understand why you don't just import the spreadsheet as the final table, but if you're happy, then so are we