Attach file to Access Client Server App

[jmeadows7]

I have an Access 2003 app that is attached to SQL Server via odbc linked tables. The application is a requisition work flow system. Everything is built and has been in production for several years. However, the users wish a little more convenience. They want to be able to upload a .pdf, .xls, .doc, etc file with the requisition.

Does anyone know of a widget or pre-written mde, etc that will allow you to save a file inside sql server? (And then download it and open it?)

 

[Blorf]

I had a similar post and did not find an answer exactly. Several folks suggested that instead of storing the document in sql, store it on a server that the users can access, and in sql, store the file path. When a user wants to open the file, you can use shell or followhyperlink to open it.

Thanks,
ChaZ

There Are 10 Types Of People In The world:
Those That Understand BINARY And Those That Don’t.

http://www.thelegacyreturns.com/

 

[jmeadows7]

Yeah, I have thought of that - thought there may be some way already written to store/retrieve as in the server. But thanks for the reply.

 

[jmeadows7]

OK - I only have SQL2000 on the machine I'm messing around on tonight. Is there an easy way to store the hyperlinks in sql server and then re-dislplay them when read from the database.

I created a local table with a hyperlink type and then used the upsizing wizard to send it to sql server. It converted it to data type ntext in SQL Server. When I pull the data back it has the file name as #D:\data\filename.doc# and doesn't hyperlink.

Any quick ideas? This just shouldn't be that difficult.

 

[Blorf]

Hi. Text is fine.

Say that x = "d:\myfile,PDF"

In a module

Followhyperlink x

Will open the file.

ChaZ

There Are 10 Types Of People In The world:
Those That Understand BINARY And Those That Don’t.

http://www.thelegacyreturns.com/

 

[MazeWorX]

I use this code to open a pdf document. I store the folder path in a table and get the contract name from the currently open record.

Private Sub cmdOpenContract_Click()

Dim strSNR As String
Dim strPath As String
Dim dbs As Database
Dim rst As Recordset
Dim strQuery As String
Dim strFolder As String
Dim FSys As New FileSystemObject

strSNR = Me![SNR].Value

Set dbs = CurrentDb()
strQuery = "qryPDF"
Set rst = dbs.OpenRecordset(strQuery, dbOpenForwardOnly)

If IsNull(rst![PDF]) Then
MsgBox "Please update PDF folder location"
Else
strFolder = rst![PDF] 'get file path from table
strSNR = Me![SNR].Value 'get contract number
strPath = strFolder & strSNR & ".pdf" 'put it all together with file ext

If FSys.FileExists(strPath) Then
Call fHandleFile(strPath, WIN_NORMAL) 'open the file
Else
MsgBox "Sorry No File Found"

End If
End If
'cleanup
rst.Close
dbs.Close

End Sub


it uses the function fHandleFile to open any file type by the originating program written by Dev Ashish and can be downloaded here

http://www.mvps.org/access/

Remember amateurs built the ark - professionals built the Titanic

[flush]