SQl server/MSAccess
I have MSAccess users that have linked tables from sql server. I want them to be able to export the linked tables from MSAccess to a different locations. So, I don't know what kind of permissions they need on SQL server side.
Define 'different locations'.
You can dump any Access to text file with DoCmd.TransferText, to a spreadsheet with DoCmd.TransferSpreadsheet,
As long as the table is successfully linked into SQL Server, even with just read only, an Access user can 'export' the data to another data source.
Hope this helps.
-Jim
You can dump any Access to text file with DoCmd.TransferText, to a spreadsheet with DoCmd.TransferSpreadsheet,
As long as the table is successfully linked into SQL Server, even with just read only, an Access user can 'export' the data to another data source.
Hope this helps.
-Jim
Also, please clarify whether you want to re-create a linked table from one MS Access database to another, or copy the data from the linked table to another data source (Excel, Text, Access, SQL, Oracle, etc. etc.)
ASKER
the user wants to export the Linked MSAccess table to a different sql server.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
<<<You can dump any Access to text file with DoCmd.TransferText, to a spreadsheet with DoCmd.TransferSpreadsheet,
>>>
and user can't create a macro or a form on the MSaccess. he doesn't have the right to do this.
>>>
and user can't create a macro or a form on the MSaccess. he doesn't have the right to do this.
The easiest way to accomplish that is not to use Client side storage: It adds overhead trafic, and make things complex. You should do the following:
1) Create and Name an ADP Access projet file on each specific SQL database that are used in your linked databases...1 ADP Access file = 1 SQL database (tables will appear as Access tables but data will NOT be stored in Access-Access is just a thick client) depending on how many db's you have this should not take more than five minutes...
for instance, if you db is named DB1 then you may name your ADP DB1.adp. Create an ADP for each DB (DB2.adp, DB3.adp....). depending on how many db's you have this should not take more than five minutes...
2) Once your ADP's (1 for each database) are set up just select any table on you Access tab then right click export. Ypu will then be prompted for selecting some file. You will then just have to select the ADP of another DB and that's it you're done (except recreating identities). For instance let's say you created 2 ADP's for DB1 and DB2 respectively. If you want to import a table from DB1 to DB2 you just have to open DB1.adp, select the table(s) you want to export, then select DB2.adp when prompted. This is a one shot operation... (it also works with Excel files , txt etc.....)
Hope this helped...
1) Create and Name an ADP Access projet file on each specific SQL database that are used in your linked databases...1 ADP Access file = 1 SQL database (tables will appear as Access tables but data will NOT be stored in Access-Access is just a thick client) depending on how many db's you have this should not take more than five minutes...
for instance, if you db is named DB1 then you may name your ADP DB1.adp. Create an ADP for each DB (DB2.adp, DB3.adp....). depending on how many db's you have this should not take more than five minutes...
2) Once your ADP's (1 for each database) are set up just select any table on you Access tab then right click export. Ypu will then be prompted for selecting some file. You will then just have to select the ADP of another DB and that's it you're done (except recreating identities). For instance let's say you created 2 ADP's for DB1 and DB2 respectively. If you want to import a table from DB1 to DB2 you just have to open DB1.adp, select the table(s) you want to export, then select DB2.adp when prompted. This is a one shot operation... (it also works with Excel files , txt etc.....)
Hope this helped...
ASKER
you mean ADP is created in Access or Sql server?
how do I create it ADP in access or SQL server?
how do I create it ADP in access or SQL server?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
any issues?