Copy external files to different locations

Hi,

I'm fairly new to SQL, waiting on a course on how to use it, but wanting to get stuck it.

I was wondering how I can get SQL to copy a file, say in Access, from one location to another.
This would be part of my job with delivering data extracts users.

Many thanks in advanceMark,

this may be a situation where you want to take a step backwards in the requirements definition process and look at the problem from a different context: what is it that your users need? While there are ways to copy data/files from point A to point B using SQL Server, there may be an entirely different approach that would be more in keeping with best practices.

As a first step, I might suggest investigating DTS (Data Transformation Services). I would also look at SQL Reporting Services (though you need a bit of experience setting this up; I wouldn't recommend it for a newbie).

You may also want to consider Access Data Projects (ADP); I don't recommend them for development, but they do provide serviceable reporting tools.

Regardless, focus on the user requirements (not the way they've always dones things, but rather what they really use the information for and how they use it). All that being said, of course you want to meet whatever need in the shortest amount of time possible. Be prepared to take an incremental approach and "lead" your users to a better solution down the road.

Regards,

hmscott|||Thanks for getting back to me.

What I have to do is take data extracts suplied to me by our IT dept and manipulate them, then supply various extracts out of this back to various teams in the company. Because of the way our place works I'd export to another database, zip it, and then move it to a shared (or sometimes protected) location each team has access to.

I used to do this via Access, but now want to up my skills and improve the way I do things.
My theory was:
1. DTS import the data (18 files)
2. Managet the data
3. Export the data
4. Zip the files and move to locations

To be honest I was just hoping it was as simple as a FileCopy but I was just missing it in the ActiveX.

If it's just not feasible yet for me at my skill level I can appreciate that, just thought I'd ask the question.

Many thanks|||I had come up with this - while it works on my C drive it doesn't seem to work across networks.

DECLARE @.result1 int
EXEC master..xp_cmdshell 'Copy "C:\ICMS\Fold1\Test.zip" "C:\ICMS\Fold2\Test.zip"'
IF (@.result1 = 0)
PRINT 'Success'
ELSE
PRINT 'Failure'|||I think a light bulb has just come on.
Is it not just the File Transfer Protocol Task.
Just tested it and seems to work fine.|||I had come up with this - while it works on my C drive it doesn't seem to work across networks.

DECLARE @.result1 int
EXEC master..xp_cmdshell 'Copy "C:\ICMS\Fold1\Test.zip" "C:\ICMS\Fold2\Test.zip"'
IF (@.result1 = 0)
PRINT 'Success'
ELSE
PRINT 'Failure'

You can use this method across the network if:
1. You use UNC naming conventions (\\SERVER\Share\folder\file.zip)
2. You SQL Server is running under a service account (not LOCALSYSTEM) and this account has write permissions on the target server.

Still, you might want to consider some other (more scalable/reliable) solutions such as:
1. ADP (already mentioned)
2. Replication to an Access database (never tried it myself, but it's supposed to work)
3. Use DTS to generate the data to the target file (DTS has export connections for Excel, MS Access and other target file types).

Regards,

hmscott

Converting to MSDE - stuck already!

I just installed MSDE on my desktop and for my first project took a copy of
one of my Access 2000 databases I have used for years and upsized it. There
were many errors, especially in the queries/views. I expected some errors.
This personal database completely resides on my PC, tables and all. When I
upsized it, it copied the tables over empty - no data. So I copied and
pasted data into the Switchboard table and others.
Then I tried to open the Switchboard form that was created automatically
when I first created the database.
This was the first programming error I encountered.
Private Sub FillOptions()
' Fill in the options for this switchboard page.
' The number of buttons on the form.
Const conNumButtons = 8
Dim dbs As Database
Dim rst As Recordset
Dim strSQL As String
Dim intOption As Integer
' Set the focus to the first button on the form,
' and then hide all of the buttons on the form
' but the first. You can't hide the field with the focus.
Me![Option1].SetFocus
For intOption = 2 To conNumButtons
Me("Option" & intOption).Visible = False
Me("OptionLabel" & intOption).Visible = False
Next intOption
' Open the table of Switchboard Items, and find
' the first item for this Switchboard Page.
Set dbs = CurrentDb()
strSQL = "SELECT * FROM [Switchboard Items]"
strSQL = strSQL & " WHERE [ItemNumber] > 0 AND [SwitchboardID]=" &
Me![SwitchboardID]
strSQL = strSQL & " ORDER BY [ItemNumber];"
Set rst = dbs.OpenRecordset(strSQL) 'XXXXX error message occurs here
XXXXX
' If there are no options for this Switchboard Page,
' display a message. Otherwise, fill the page with the items.
If (rst.EOF) Then
Me![OptionLabel1].Caption = "There are no items for this switchboard
page"
Else
While (Not (rst.EOF))
Me("Option" & rst![ItemNumber]).Visible = True
Me("OptionLabel" & rst![ItemNumber]).Visible = True
Me("OptionLabel" & rst![ItemNumber]).Caption = rst![ItemText]
rst.MoveNext
Wend
End If
' Close the recordset and the database.
rst.Close
dbs.Close
End Sub
I get a run time error 91, object variable or With block variable not set.
Help says for error 91: First you must declare the object variable. Then you
must assign a valid reference to the object variable using the Set
statement.
I have declared and set both the database and recordset.
Since I am totally new to MSDE, can anyone guide me to where I can find out
more about this and other errors I am sure to encounter?
Thanks,
Mich
I suspect that your problem is occurring with the statement
Set dbs = CurrentDb()
How are you connecting from Access to the MSDE tables?
Chuck Heinzelman - MCSD, MCDBA
(Please respond only to the newsgroups.)
I support the Professional Association for SQL Server and its community of
SQL Server professionals.
www.sqlpass.org
"M Skabialka" <mskabialka@.NOSPAMdrc.com> wrote in message
news:uGv1gzqGFHA.3628@.TK2MSFTNGP15.phx.gbl...
> I just installed MSDE on my desktop and for my first project took a copy
of
> one of my Access 2000 databases I have used for years and upsized it.
There
> were many errors, especially in the queries/views. I expected some
errors.
> This personal database completely resides on my PC, tables and all. When
I
> upsized it, it copied the tables over empty - no data. So I copied and
> pasted data into the Switchboard table and others.
> Then I tried to open the Switchboard form that was created automatically
> when I first created the database.
> This was the first programming error I encountered.
> Private Sub FillOptions()
> ' Fill in the options for this switchboard page.
> ' The number of buttons on the form.
> Const conNumButtons = 8
> Dim dbs As Database
> Dim rst As Recordset
> Dim strSQL As String
> Dim intOption As Integer
> ' Set the focus to the first button on the form,
> ' and then hide all of the buttons on the form
> ' but the first. You can't hide the field with the focus.
> Me![Option1].SetFocus
> For intOption = 2 To conNumButtons
> Me("Option" & intOption).Visible = False
> Me("OptionLabel" & intOption).Visible = False
> Next intOption
> ' Open the table of Switchboard Items, and find
> ' the first item for this Switchboard Page.
> Set dbs = CurrentDb()
> strSQL = "SELECT * FROM [Switchboard Items]"
> strSQL = strSQL & " WHERE [ItemNumber] > 0 AND [SwitchboardID]=" &
> Me![SwitchboardID]
> strSQL = strSQL & " ORDER BY [ItemNumber];"
> Set rst = dbs.OpenRecordset(strSQL) 'XXXXX error message occurs
here
> XXXXX
> ' If there are no options for this Switchboard Page,
> ' display a message. Otherwise, fill the page with the items.
> If (rst.EOF) Then
> Me![OptionLabel1].Caption = "There are no items for this
switchboard
> page"
> Else
> While (Not (rst.EOF))
> Me("Option" & rst![ItemNumber]).Visible = True
> Me("OptionLabel" & rst![ItemNumber]).Visible = True
> Me("OptionLabel" & rst![ItemNumber]).Caption = rst![ItemText]
> rst.MoveNext
> Wend
> End If
> ' Close the recordset and the database.
> rst.Close
> dbs.Close
> End Sub
> I get a run time error 91, object variable or With block variable not set.
> Help says for error 91: First you must declare the object variable. Then
you
> must assign a valid reference to the object variable using the Set
> statement.
> I have declared and set both the database and recordset.
> Since I am totally new to MSDE, can anyone guide me to where I can find
out
> more about this and other errors I am sure to encounter?
> Thanks,
> Mich
>