Ok so I have built a database that is used to house and manipulate records that I get from a monthly report that is posted online. Basically I work for the Army and we have over 50 units. Each unit has anywhere from 10 to 1000 soldiers in it. I initially had tried to build a database with a table for each unit but that wasn't working out fro queries and just wasn't design friendly. What I've done now is built one table to house all the soldiers and a query which puts in what unit the soldier is from when you paste the names in a form. Unfortunatly I cannot do a mass download of each report. The only way to pull it is to go to each units report and copy the records. It's an Army program and has all kinds of security stopping me from doing anythin other then copying the records. I need to be able to then paste them into my Access database so I can create reports and other stuff like that. The way the website table is formatted causes it to be pasted in one field when I try to Paste or pasteappend directly into Access. The only way that I have found to paste it in Access is to paste it in Excel first then copy and paste it in Access. I can not do the whole import from Excel thing cause I need to make this database as automated and user friendly as possible so telling the other users they have to open an Excel sheet paste it in there then copy it go back to Access and paste it again in their won't work. I don't know how else to explain it but basically I'm trying to stay away from using a separate Excel document if at all possible. Here's what I've done: I have created a form called "Paste" with an OLE unbound Excel worksheet on it called "PasteRecords". I have another form called "Main" which I want to be the main user interface. Basically what I want to do is have the user go to the website copy the records, then go to the "Main" form and click a command button called "PASTEALLCMD" which will open the "Paste" form delete whatever is already in the OLE unbound Excel worksheet ("pasterecords"), paste whatever is on the clipboard, select it all, copy it, and then close the "paste" form. I have changed the properties of the OLE Unbound object so it is enabled, unlocked, and Auto Activate's upon Get Focus. Basically when you open the form "Paste" it is ready to have the records pasted but I can't figure out a code to do this automatically without the user having to do anything. I know it sounds wierd but I have tried everything else and the only way I can find to get these records pasted in the Access table is to first have them go through Excel (I'm guessing pasting them in Excel is reformating the date and allowing Access to read them as individual cells rather then one long string).
I'm using Access 2007. PLEASE help!