How to Upload E-files from ms access to sql server and download it

[masoud_sedighy]

I have upsized my access database to sql server. Now there are linked tables from ms access (FE) to sql server (BE), I would like to upload files in sql server from ms access (FE) and can view and download also from ms access (FE). I would like to know what changes i have to do in design of sql server table and what codes and change design i have to add to ms access (FE).

 

[Rx_]

Lets break the questions down to bite-size.
1. You upsized
2. The linked tables from the FE to the BE are working?
3. Explain the needs to change design SQL Server (give us some examples)
4. Code on the FE to meet the back end

RE: 4 I prefer to create a local FE table that holds the names of tables in the BE.
Then, use code to read the local table name - and automate the relink to the BE.

Question: Is your DB for one single user or for multiple users?

 

[masoud_sedighy]

Thanks a lot for break down my question actually i do not know what is the procedure and sequence if i want to upload E-files from ms access to Sql server and then can view that from ms access.

What i know is just a very a little about
1-upsize
2_ADO
3-filestream
4-filetable

But i do not know how i have to use them for this job and what is the sequence for using them.

DB is for multiple user.

Actually i have a access database that i have spilitted it and it works. But always i have problem about E-files.
i have put in my form a text box and button , we can select E-file from the shared folder in the network and so keep path address of E-file in the text box, and then with using this path and file name i could hyperlink to the file in the network.
After reading some article about file stream and file table in the google i would like to upgrade that to sql server and keeping E-files with Sql server and can access from ms access FE. But i do not know about the sequence and procedure for doing this job.

What i can do if use a lot of time, is just making sql server database with the same design tables and relatinship in my access file can link from FE to sql server_be, but
1- i do not know alot about file stream and file table and what design changes i have to add to sql server.

2- i do not know how i have to connect FE ms access to Sql server with code , i have read about ADO a bit but i have a lot of problem in action and what have to do from start until end.

I will be appreciate if guide me about the sequence i have to do. That guide me from start point until end point.

 

[Rx_]

Since the upsizing - look at your FE table View.
Do they resemble Globes (indicating SQL Server) with arrows (indicating linked tables)?
In the FE table objects, double click (open) a linked table.
Does the table's data appear?
Scroll down to the last record (the one with a *). Can Data be entered?
After entering a record, highlight the entire last recently entered record (click on the most left border, highlight the entire last record), press Delete. Can record be deleted?

If all of this works, my suggestion is to download your e-data to a new LOCAL table (lets call it MyEDataImport).
Forget ADO at this point.
Then create:
Import the edata into the local table MyEDataImport. Perform any data cleanup.
An Append query using the MyEDataImport Table as the source and update the Linked Table where the data should reside.
A Delete Query to delete the last e-data in the MyEDataImport Table.

The linked tables can take care of the heavy lifting.
By breaking the task into multiple sequential steps, it increases the odds of success and makes troubleshooting easier for each step.

 

[masoud_sedighy]

If it is possible please explain more about importing E-data in local table.

You mean I have to create the local table (MyEdataImport) in sql server and then making a link from ms access FE to that?

About importing E-data into local table, you mean puting E-files in "File table" of sql server?

 

[masoud_sedighy]

I found a similar question in the below link, i hope it helps me:

https://www.experts-exchange.com/questions/26923910/How-to-save-and-read-files-stored-as-FileStream-in-SQL-Server-2008-from-MS-Access-2007-vba.html

 

[Rx_]

With a FE and BE, tables can reside in two locations.
1. Shared data for all users on SQL Server - each FE has a Linked table to SQL Server.
2. Local Data for a specific user during a session - Use the Local Access Table



If you have Upsized, more than likely all of your tables are now linked to SQL Server.
Lets get back to your overall objectives.

How many users will use your FE to share the BE on SQL Server?
How will the FE be distributed to the Users?
- This will determine the new FE version release plan
Will a FE import data from an external data source?

While the upsizing wizard was automatic, the architecture is not automatic.