login and copy files to win2000's harddisk DIRECTLY by code

If you're dealing with a network or VPN, easy fix.  

If your users have their own private directories available on the server (such as domain-based home directories), provide a mapping for it on the client machine.  Otherwise you'll have to create whatever directory structure for your particular security needs.

After that, just have the code copy the file locally from the user's drive to the mapped drive.  Since it's mapped, Access considers it another drive in the system catalog.  Should be just like copying a file from hard drive to floppy.

I am dealing with a network. but users don't have private dirs. the server the users login to is novell. I can map a drive there on both the client and the win2000 server. but the problem is that many users will log in to the access app, and it is the *SQL* user they use to login to the sql tables that determine the files they can view, *not* the novell login.


I am using now a mapped drive, but all users see all files..

Try a sample bit in access to copy a file from place to another.

FileCopy "c:\myfilename.blah", "z:\newfilename.blah"

If you can get snippet to work with your particular network, your job is done.  :)

routinet,

I am using that method. the problem is I want somehting else:

now the files are being copied from the local harddisk to a mapped drive.

I want them to be copied from the local harddisk to a directory on the win2000 server, that the users CANNOT see. Only my app sees it by loging into the win2000 by code.

hrrmmm..that's a little more tricky.

The only solution that comes to mind is a back-end app to do the job.  If I have your scenario right, users work on a database at their workstations.  Files they upload are copied to a mapped dir on a server.  You want these files to be copied to a particular user's directory on a server they can not see on the network.

Create a small back-end app.  It will need access to both the server with the files and the 'private' server that users cannot see.  Any time your Access database receives a file from the user, pass the source and destination paths to the back-end for the final copy.

how about an FTP code written inside the access app? it would ftp to the win2000 server (who is the sql server at the same time), copy the files and do the job.

FTP would certainly do the trick, but your original question steered away from that solution.

If the server is an SQL server, why not create a stored procedure there to do the job?  It should have no problem authenticating for security, it will be transparent to the users, and you can trigger (by using sp's or triggers) any time you want.

that really sounds like a great solution

haven't done an SP before, can you guide me? :)

thanks,
kay

you can use an extended stored procedure

something like this might work...

declare the input variable...set this on the vba side of the code with something like
varPath = "c:\myDir\MyFile.gif"
strSQL = "EXEC spcopyMyFiles '" & varPath & "'" ' of course you'll need the whole connection string...depends on if using ado or dao

for ado it would be like

       Set cnn = New ADODB.Connection
        strSQL = "EXEC spcopyMyFiles '" & varPath & "'"
        cnn.ConnectionString = pubstrConnect  'this is your connection string
        cnn.Open
        cnn.Execute strSQL
        cnn.Close



create dbo.sp_copyMyFiles

DECLARE @path varchar(250)

as

EXEC xp_cmdshell 'copy' + @path + '\\myserver\dir\'   NO_OUTPUT




should work with a bit of tweaking

err....except your

varPath would be need to be machine specific

get the machine name http://www.mvps.org/access/api/api0009.htm

then use the function
varFileName = me!MyFilePicker  'set this to what ever you want to move
varMachineName = fOSMachineName()
varPath = "\\" & varMachineName & "\" & varFileName

and you'll need to add some spaces to the EXEC string so it forms a proper command string

                                  V here          V here
EXEC xp_cmdshell 'copy ' + @path + ' \\myserver\dir\'   NO_OUTPUT


you could also set the destination as a variable from vba side if you wanted...

After some work, I got this on the vb side:

varMachineName = fOSMachineName()
varPath = "\\" & varMachineName & "\" & "C\ver.txt"
txttest.Value = varPath

Set spconn = New ADODB.Connection
spconn.Open "Provider=SQLOLEDB;Data Source=x.x.x.x;User ID=xx;password=xx;Initial Catalog=xx"
strsql = "EXEC sp_copyMyFiles '" & varPath & "'"
Set rstsp = spconn.Execute(strsql)
spconn.Close


and I wrote this in the SP:

CREATE procedure dbo.sp_copyMyFiles
as
DECLARE @path varchar(250)
EXEC master..xp_cmdshell "copy @path \\bagheera\d\"
GO


FIRST, let me say that the sentence
EXEC xp_cmdshell 'copy' + @path + '\\myserver\dir\'   NO_OUTPUT


doesn't work, syntax error on the + and NO_OUTPUT. I tried &, same thing. So first thing I replaced it with, just to test:
EXEC master..xp_cmdshell "copy @path \\bagheera\d\"


NOW, if I leave it like that, it tells me the stored proc doesn't require any parameters. So if I remove the parameter in the calling line just for test:
 
strsql = "EXEC sp_copyMyFiles"
instead of
strsql = "EXEC sp_copyMyFiles '" & varPath & "'"

then, I get the error:
Run-time error '-2147467529 (80004005)':
xsql.cpp: Error 87 from GetProxyAccount on line 604

So, it is not working and I don't know why. Additional "unhappy" comments I have about this are:
- I had to use username password in the adodb eventhough the clients will have ODBC, is there a way to use the computer's ODBC connection without having to mention usernames and passwords? (don't give answer, just say yes or no, I will ask it in another question).
- I got permission denied on xp_cmdshell so I had to give exec permission to the public for this sp in master. Isn't this bad? Can't I give it to this particular user only? (there is only guest and public).

Maby just another idea.

Don't give any user access to the share, let access arange the links and let access save the file on a specific location with the saveas dialog.  Therfore you have to build a rights system into your access database, create a user on the server for the access database in that way the access databs is only user who can download teh file. the user clicks a button in the form the access db opens the link, open the save as dialog , save the file , open it done!

just an idea

greetings, fcco

I shared the directory with administrators
no error message is being given.. but no file is being copied...

how can I catch that Debug.Print strSQL string?

I diagnosed the problem.

it can't access my localdrive. the drive of the local machine. if I go Start->Run->cmd

and type:
copy \\mymachine\c\test.txt \\mymachine\c\test2.txt

it says can't find path

if I type:
\\mymachine

I get a box but without any shared drives. That is a problem..

strSQL = "EXEC sp_copyMyFiles '" & varpath & "'"
Debug.Print strSQL 'sb here...not on line above

   
 copy \\mymachine\c\test.txt \\mymachine\c\test2.txt

if you've shared the folders don't use a drive letter in the path and i wouldn't recommend sharing the whole c drive

 copy \\mymachine\myshareddir\test.txt \\mymachine\myshareddir\test2.txt

well, since this app will run from clients accross the building, I can't have a shareddir on each of their computers...

that is a flaw in this logic..

maybe copy it temporarily to a mapped drive that all the building can see..
hmm let me see..

why not to another temp directory on the clients computer?? instead of a network share

there must be a better design than having to share a dir on every computer...
and different operating systems yield different "automatically shared drive" etc..

if the account you use to run sql server is in the administrators group you could probably use the administrative share and not have to specifically share any folders

copy \\mymachine\c$\test.txt \\myserver\folder\test2.txt

i haven't tested this and don't know the full security implications of this but i think it would work...


perhaps a better solution would be to create a dir with all users having subfolders on the server with permissions for that user and the sqlserver startup acct.

so if "bob" the user and "dba" start up acct have full permissions on dir_bob they can both access and the code should work...

copy \\server\dir_bob\test.txt \\server\theMainFolder\test.txt