Check if file exists on SQL Server? 1

[christywarner]

hi,

I have an Access front-end w/ an SQL Server
back-end, and want to know how I can check to
see if a file exists on the SQL server?

My code gives me an error when the file
doesn't exist:

Dim rs As ADODB.Recordset
Dim cn As ADODB.Connection
Dim db As Database
Dim cstring As String

Set rs = New ADODB.Recordset
Set cn = New ADODB.Connection

cstring = "provider=MSDASQL.1;User ID=guest;password=; Initial Catalog=Master; Data Source=cwsqlserver"
cn.ConnectionString = cstring
cn.Open

rs.ActiveConnection = cn
rs.Open

how can I check to see if the file exists
before calling the "rs.open"?

thanks!
Christy.

 

[Craig0201]

Need more clarity here. What file? I doubt you're talking about MDF and LDF files. Are you talking tables? Views?

Craig

 

[christywarner]

I want to check the SQL server to see if
a file exists; then if it does, I want to
delete it.

thanks!

 

[Craig0201]

Doesn't answer which file. Either you're not posting all your code or you don't mean file.

Are you talking about the name of a file being stored in the DB?

Craig

 

[cmmrfrds]

Why do you want to delete (drop the table) from the Access App? Are you the database owner?

 

[christywarner]

Hi All,

I figured it out, and I thank you for your replies.
Here is how I did it for those who would like to know.
I created a Stored Procedure on the SQL Server side,
calle EXISTDROP:


CREATE PROCEDURE EXISTDROP AS
IF EXISTS (SELECT name FROM sysobjects
WHERE name ='NEWTABLE')
DROP TABLE NEWTABLE
GO

Then from inside my Access app, my code calls
the stored procedure:

This is so I don't get any errors on
my "SELECT...INTO newtable" statment.

Private Sub ConnectToSQL_Click()

DoCmd.SetWarnings False

Dim rs As New ADODB.Recordset
Dim rs2 As New ADODB.Recordset
Dim cn As New ADODB.Connection
Dim cstring As String
Dim cmd As New ADODB.Command

Set rs = New ADODB.Recordset
Set rs2 = New ADODB.Recordset
Set cn = New ADODB.Connection

cstring = "provider=MSDASQL.1;User ID=sa;password=; Initial Catalog=master; Data Source=cwsqlserver"
cn.ConnectionString = cstring
cn.Open

cmd.ActiveConnection = cn
cmd.CommandText = "EXISTDROP"
cmd.CommandType = adCmdStoredProc
Set rs = cmd.Execute

rs2.ActiveConnection = cn
rs2.Open "SELECT appayments.finvoice into newtable FROM appayments", cn
cn.Close

End Sub

 

[seleceus]

I have a use for such a thing, provided it's not a SQL object.

I'm using an Access FE to import, into another Access BE, from a .csv. Well, I didn't want the routine to error on every automated run, because the file hasn't been produced by a remote routine, so I used a variation on the following:

If Len(Dir('path+filename')) = 0 Then
'file, or path, is not there
Else
'file, and path, is there so do something
Kill "path\filename"
End If

Hope this helps.

 

[cmmrfrds]

Just a couple of FYI.

This is using the default Microsoft SQL Server Provider that also goes through the ODBC driver which is an unnecessary step.
cstring = "provider=MSDASQL.1;User ID=sa;password=; Initial Catalog=master; Data Source=cwsqlserver"
Use instead.
provider=SQLOLEDB.1; 'this is the native provider.

I am surprised your DBA lets you use the 'sa' login if you are going to roll this app out to other users. Also, normally the stored procedure would be part of the App and in the App database not in the Master database.

After closing the connection also destroy the connection or it will retain the memory.
cn.Close
Set cn = Nothing

 

[Craig0201]

Seleceus,

If Len(Dir('path+filename')) > 0 Then
Kill "path\filename"
Do While Len(Dir('path+filename')) > 0
DoEvents
Loop
End If

is neater (no "missing" code) and the Do loop ensures that the function runs until the file has been deleted, not until the file delete request has been made.

Craig