What would your approach be. Multi-user, no network.

[The_Vincester]

I recently pick up the new version of Office for personal use. I built quite a few DBs for work using 2003, and the developers extensions, but didn't have anything for home use. I saw where MS was releasing the 2007 runtime for free and got pretty excited.

Now I'm ready to tackle my first 2007 project that will be distributed and I've already stumped myself.

Basically I'm going to build a fantasy football db for some friends. The actual design isn't a problem, I could do that easily. My issue are the stats. I really think I need an import option somehow, but not sure how to go about it. First I have to assume that they don't have Office so using Excel files are out. I guess I can use .csv files, but we're talking several different types and categories of stats... QBs , RBs, WRs.

I have to consider the method of compiling those stats where it's somewhat easy to compile the stats, covert them to whatever format I want to import and then make it easy for them to import.

This one really has me stumped!

The easiest way about this would be a web-based backend option I think, but I don't want to worry about hosts and things like that.

My thought right now is one person will have to have a full version of Access, with instruction on how to do an import, do some appends then after it's all said and done, zip up the backend and send it out to the league via email, where they're replace their old backend.

It's really a messy affair. Any ideas?

 

[The_Doc_Man]

The idea of attaching your stats file to e-mail is probably the easiest way if you cannot assure a common server.

As to zipping things, a .TXT or .CSV file is OK. When you do this, practice a little first.

Build your tables of things you want to export.

Then export them to your chosen file format. Build an output specification for this export. You will probably want to have more than one table so you will actually need several output specs. Save each output spec when you get it right. Think "query" when exporting, since it often is a better way to assure output formats.

Now practice importing them using corresponding import specifications. You can again save the spec when you are done with it. You will have many input specs too.

Now look at a macro as the way to run multiple imports. All you would need to do is plop down the zip file in the folder with your MDB file. When you export, always export to the same names. When you import, always import to the same names. When you send mail, tell your folks to import the zip file and unzip in the directory where the MDB is found.

Now... here's a trick. The spec of your current database is CurrentDB. This is a shortcut to the database, but it has properties you can see just like a real database variable type - as though you had used a WorkSpace.OpenDataBase operation. OK, where this is going: CurrentDB.Name is the fully qualified file spec of your MDB file. So... using InStr you can find the last / in the spec. Everything to the left is the device and path. Everything to the right is the name and type. So you can find the path with a little bit of VBA. And that is where your import files will be. So you can tack the name and path together to specify each file.

The trick is to make this "faceless" wth respect to the folder it is in. Otherwise I would say just force everyone to store it in a fixed location on the C drive, which also works unless they have an overly full C drive. But that is your call and requires assent from all who participate.

 

[CraigDolphin]

CurrentDB.Name is the fully qualified file spec of your MDB file. So... using InStr you can find the last / in the spec. Everything to the left is the device and path. Everything to the right is the name and type. So you can find the path with a little bit of VBA.

You can also use CurrentProject.Path to obtain the same thing in Access 2000/2003...I assume it still works in 2007.

 

[The_Doc_Man]

Since I used the older method and got used to it, never looked for something newer. Thnks for the update, craig.

 

[CraigDolphin]

Glad I could return at least one of the many favors I owe you, Doc_Man

 

[odin1701]

If they don't have office, then what are they going to open your database with?

 

[The_Doc_Man]

good catch, odin. Missed that one.

Vincester, if your folks don't have Office and you didn't build a run-time version using a developer's kit, they won't be able to open the DB anyway.

 

[Villarreal68]

I know this is an old link, but I thought I would just make a quick comment pertaining to the import of data.

Could they not link .csv files as their tables? This way the person with the Full Access app can just export the data to csv files and have the remote users (with no Office apps) replace the .csv files before opening the db and everything would be peachy fine?

Would there be anything wrong with that? I could be wrong but, just a thought.

René