Attachments in Access 2003

[Michelle F]

Looking for some input....

I have built a db for my supervisor. It is to be a log of all employee
activities. He has asked me to set it up such that when an employee fills out
a form about an activity he (the employee) can add several attachments to
that activity record, like documents or emails, etc. Is this possible in
Access 2003? I looked into embedded objects, but I can't see how I can get
the nice browse/attach popups like with email programs.

And I think it has to be an embedded object stored within the db (but am
open for suggestions!) because my boss has also asked that the db be portable
when he travels so he not only has access to records but information
pertaining to those records. If I just set it up to link documents and such
to an activity, I think it wouldn't work for him to download the db to his
laptop because all the filename extensions would be broken. I recognize that
embedding is going to take up HUGE amounts of space, but this is a feature my
boss is insistent on.

As a side note, I currently have all my tables built and stored in SQL
server and then linked to my Access db. I was told by our IT guy that this
was the best way to maintain date integrity, but it becomes a problem (once
again) when my boss wants to get a copy of the db for travel.

Any thoughts about these issues would be very much appreciated!

Thanks! Michelle

 

[vbasean]

Looking for some input....

I have built a db for my supervisor.  It is to be a log of all employee
activities. He has asked me to set it up such that when an employee fills out
a form about an activity he (the employee) can add several attachments to
that activity record, like documents or emails, etc.  Is this possible in
Access 2003?  I looked into embedded objects, but I can't see how I can get
the nice browse/attach popups like with email programs.

And I think it has to be an embedded object stored within the db (but am
open for suggestions!) because my boss has also asked that the db be portable
when he travels so he not only has access to records but information
pertaining to those records.  If I just set it up to link documents and such
to an activity, I think it wouldn't work for him to download the db to his
laptop because all the filename extensions would be broken.  I recognizethat
embedding is going to take up HUGE amounts of space, but this is a featuremy
boss is insistent on.

As a side note, I currently have all my tables built and stored in SQL
server and then linked to my Access db.  I was told by our IT guy that this
was the best way to maintain date integrity, but it becomes a problem (once
again) when my boss wants to get a copy of the db for travel.  

Any thoughts about these issues would be very much appreciated!

Thanks! Michelle

Opinion of one lowly novice:

An Access db linked to sql server tables will not be portable

An Access db that has enbeded files will grow quick and I believe
Access has a 2 gig limit

An Access db with links to files would take a bit of time to grow to 2
gigs. You would have to port the folder where the files reside to
have a complete solution.

I don't think that's too hard to do, porting the folder.

Have the form copy the file to the folder and rename it for storate/
retreaval. A button linked to a file dialog box solution would
probably work well.

Then some vba that copies the file and renames it would do the trick.

building a string to create a unique file name like:
EmployeeID & "~" & ProjectID & "~" & FileName

 

[aaron.kempf]

It isn't that hard to replicate from a central SQL Server to a desktop
MSDE instance.

It just depends on the size of the data (as to whether it would fit in
MSDE).

I'd personally-- build an ASP application or something; so that he
could continue to look at this data (remotely) using this invention..
called the 'internet'.

-Aaron

 

[Michelle F]

Thank you both for the input!

vbasean:

Maybe you could expand on this idea of "porting"? I am concerned that if the
files are originally saved and linked is on our server (let's call it S://).
Then, if my supervisor copies the folder to his laptop (C://) and the
database and tables from SQL, all of the links would be broken because they
would refer to the location on S:// and not on C://.

Aaron:

Is there an easy way (maybe writing a macro) to automate coping tables from
SQL on our server to his laptop? My current (long) method is to import all
the linked tables and rename them to the original linked table names so the
reports and forms still work. But with 20+ tables this is a bit cumbersome.

I also agree that using the internet or connecting to our network remotely
would solve all problems! But apparently that is not an option during many
meetings when this information would be needed.

 

[aaron.kempf]

yes, you can just use 'Access Data Projects'. and have SQL Server on
his laptop.

And simply right-click 'import'.

That way; you don't need to rename anything with DBO_

-Aaron

 

[aaron.kempf]

btw; I don't think that renaming 20 tables- programmatically or by
hand- would take me 30 seconds

Dim tbl as AccessObject
For each tbl in CurrentData.AllTables
If left(tbl.Name, 4) = "dbo_" then
Docmd.Rename table, tbl.Name, replace(tbl.Name, "dbo_", "")
End if
Next tbl

 

[aaron.kempf]

and btw; embedding the files inside of SQL Server isn't nearly as
inefficient as in Access.

with SQL Server, you can also use the varbinary datatype if all of
your files are small. This is _TONS_ more efficient than Access.

-Aaron

 

[Michelle F]

Aaron,

I think that sounds like a great idea. I worked this afternoon with an IT
guy about using Access Data Projects (I had never heard of it before you
mentioned it).

Do you know if there is an easy way to take the db "offline"? My supervisor
is pretty insistent that he wants to be able to get all the information
without being connected to the internet. I told him about the 2GB limit but
our current db is 23kB (with no attached files) so it may be alright to just
install MSDE and SQL on his laptop and then get the data offline. But I
warned him that in the future that may not be an option as the db grows.

Thanks again! Michelle

 

[Tony Toews [MVP]]

An Access db that has enbeded files will grow quick and I believe
Access has a 2 gig limit

That bloat applies to graphics files such as jpgs as Access creates
bmp copies of the graphic which are very large in comparisons to the
original jpg.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

 

[Tony Toews [MVP]]

And I think it has to be an embedded object stored within the db (but am
open for suggestions!) because my boss has also asked that the db be portable
when he travels so he not only has access to records but information
pertaining to those records. If I just set it up to link documents and such
to an activity, I think it wouldn't work for him to download the db to his
laptop because all the filename extensions would be broken. I recognize that
embedding is going to take up HUGE amounts of space, but this is a feature my
boss is insistent on.

Stephen Lebans has some interesting VBA code at his website dealing
with OLE and such that may be exactly what you need.
http://www.lebans.com/oletodisk.htm

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

 

[Tony Toews [MVP]]

I think that sounds like a great idea. I worked this afternoon with an IT
guy about using Access Data Projects (I had never heard of it before you
mentioned it).

You should be advised that Aaron's answer to every question requires
the use of ADPs and/or SQL Server.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

 

[aaron.kempf]

Yes, to the OP-- ADP rock. I'd love to tell you more about it.
You can use 'best in breed' database tools while still using existing
forms and reports

You should be advised that Tony is a mental midget that enjoys
'getting people stuck with midget sized databases'.

Tell me-- Oh Great Tony-- How do you _REPLICATE_ with Access 2007?

Tell me, asshole-- instead of talking shit-- tell me how to replicate
with Access 2007 -OTHER- than ADP ok, prick-breath??!!??

 

[David W. Fenton]

You should be advised that Aaron's answer to every question
requires the use of ADPs and/or SQL Server.

Yes. Aaron uses them to wipe his ass.

 

[aaron.kempf]

If _I_ wanted to avoid MDB Bloating; I would move to SQL Server! I
mean get real!

 

[aaron.kempf]

Please be advised that Tony is a bully; a liar and a cheat.

Thanks

-Aaron

 

[aaron.kempf]

Warning

David Fenton makes inaccurate and incorrect statements.

I would reccomend finding a certified SQL Server DBA or Developer in
order to fit your database needs.

This 'D A V I D F E N T O N' kid learned his databases on the back of
a cracker jacks box

-Aaron Kempf
MCITP: DBA