import a query from a text file

[Ivan Grozney]

I have a need to run something like OSQL for SQL Server. I would like to
have a script (VBS or ???):
1. fire up Access and open a database
2. import a query from a txt file
3. run the delete query
4. close the database
5. exit access

This is a vendor generated database and it is for each user. So in one area
we might have 50 people access the PC in the course of their duties and each
one has an Access MDB file in their Application Data folder.

Is there such a thing?

tia

Vanya

 

[John W. Vinson]

I have a need to run something like OSQL for SQL Server. I would like to
have a script (VBS or ???):
1. fire up Access and open a database
2. import a query from a txt file
3. run the delete query
4. close the database
5. exit access

This is a vendor generated database and it is for each user. So in one area
we might have 50 people access the PC in the course of their duties and each
one has an Access MDB file in their Application Data folder.

Is there such a thing?

tia

Vanya

I'm sorry, this isn't making any sense.

A Query is like a View in SQL. It has no independent existance. You can't
"import a query".

Or do you mean that you have the SQL text of a query stored in a text file
that you wish to execute?

What's the context? If the text file does contain the SQL of a Delete query,
can you (with great confidance!) say that it just deletes that user's data and
nothing else?

 

[pietlinden]

Assuming such a thing is safe (may well be a dangerous assumption), it
might be possible to open a text file, read the contents, stuff them
into a string variable and then assign that value to a querydef... not
very safe, but possible...

 

[pietlinden]

I have a need to run something like OSQL for SQL Server.  I would like to
have a script (VBS or ???):
1. fire up Access and open a database
2. import a query from a txt file
3. run the delete query
4. close the database
5. exit access

This is a vendor generated database and it is for each user.  So in onearea
we might have 50 people access the PC in the course of their duties and each
one has an Access MDB file in their Application Data folder.  

Is there such a thing?

tia

Vanya

I am not saying this is safe or wise, but it is doable. The problem
with running unchecked SQLs in your database is that someone could be
really funny and throw in a few DROP TABLE statements and your
database would get pretty much destroyed. If these are SQLs you can
trust, then you can do it...

Public Sub ImportFile(strPath As String)
' mostly stolen from a Joe Fallon post from way back...
Dim db As Database
Dim qdfNew As QueryDef
Dim sLine As String, sTrimmed As String
Set db = CurrentDb


Open strPath For Input As #1

'Read a single line from an open sequential file and assign it to
a String variable.
'Line Input #1, sLine
'Trim the leading blanks
'sTrimmed = LTrim(sLine)

Do While Not EOF(1)
'read the next line of the file
Line Input #1, sLine
sTrimmed = sTrimmed & " " & LTrim(sLine)
Loop

Set qdfNew = db.CreateQueryDef("ImportedQuery", sTrimmed)
DBEngine(0)(0).Execute "ImportedQuery", dbFailOnError
Set qdfNew = Nothing
Set db = Nothing
End Sub