Access 2007 to SQL server file upload?!

[RMcCallan]

I have an Access 2007 database with an attachments facility. Currently the client may upload files locally but the files cannot be accessed elsewhere. I have been able to carry out a similar operation when developing on a web based system however I cannot seem to do it on an Access 2007 database and I am unsure as to whether it is even possible. Basically the system needs to connect to the SQL server online and upload the file although the database is not online itself. I would be grateful for any pointers!

Thanks,
Rebecca

 

[TheBugSlayer]

Look for OPENQUERY and OPENROWSET in SQL Server Books Online (BOL).

MCP SQL Server 2000, MCTS SQL Server 2005, MCTS SQL Server 2008 (DBD, DBA)

 

[RMcCallan]

Thanks for the reply, I have done this but cannot see which part of this would help me. A pointer in the right direction would be appreciated! Thanks

Thanks,
Rebecca

 

[TheBugSlayer]

Do you mean that you need to export from Access 2007 into SQL Server? What' s "attachments facility" an Access table? Which database is not online, SQL Server?

Access will not "upload" a file to SQL Server unless its imported into an access table...If it' s imported in Access, you should be able to create a DSN for the SQL Server connection and import the data into a SQL Server table. It should be able to record this into a macro that you can execute on demand. But I am still not certain about the sequence of events...

MCP SQL Server 2000, MCTS SQL Server 2005, MCTS SQL Server 2008 (DBD, DBA)

 

[RMcCallan]

By an attachments facility I mean a table which allows the client to make an attachment to the record they are editing by browsing for the file. Currently there is a table which records the path which the client browses to and then when they wish to open the attachment, it automatically opens it in that location. The database is not online currently and is not yet connected to an SQL server however we do want to connect it to one but only for the attachment upload so that the client can access the attachment wherever they are, not just locally as is currently the case.

Thanks,
Rebecca

 

[simian336]

Are you wanting to upload the attachment as a seperate file or incorperate the file into a table field?

Simi

 

[RMcCallan]

Cheers for the reply. Umm, probably put the attachments into a table but not sure really which would be better?

Thanks,
Rebecca

 

[TheBugSlayer]

Unless you want to share a folder over the internet, your other option is to save the image in a table colum as suggested by RMcCallan, if the size is reasonable. You could use the IMAGE or VARBINARY(MAX) data types. Here we save logos in the database and the front-end to save, retrieve and display them.

In case you need it, below is an example on how to insert and update image fields in SQL Server:

---Insert
INSERT INTO YourTable (LogoLarge) 
SELECT LogoColumn from Openrowset( Bulk 'C:\LOGO.JPG', SINGLE_BLOB) as Logo

--Update
UPDATE YourTable SET LogoLarge = 
(SELECT Logo.* FROM OPENROWSET(BULK 'C:\LOGO.JPG', SINGLE_BLOB) Logo)

The path is local to the SQL Server machine, not yours.

MCP SQL Server 2000, MCTS SQL Server 2005, MCTS SQL Server 2008 (DBD, DBA)

 

[RMcCallan]

Thanks however I didn't say the files would be images. They could be anything from word docs to excel spreadsheets OR possibly images but it is unlikely that they would be images in actual fact. Would your suggested option still work in this case? Sorry, my fault for not mentioning the likely file types.



Thanks,
Rebecca

 

[TheBugSlayer]

Yes, it should work with any type of files. Just pay attention to the datatype.

MCP SQL Server 2000, MCTS SQL Server 2005, MCTS SQL Server 2008 (DBD, DBA)