VBA Run external .sql Script

[Movian]

Hey,
i am trying to implement a new feature to our auto update system. when a new version of our front end system is downloaded it checks to see if there is a table update script. If found it currently uses winhttp to request the file which is a .sql from our server and store the returned text in a string (its rather large though at 711704 chars and 24456 lines.

My thinking was that i could then just run this SQL command using the following code. However this does not seem to work, while if i execute the contents in management studio it executes correctly.

Here is a sample of the some of the contents of the SQL file and the code I'm using to execute. If anyone has any suggestions, I'm thinking i could download the whole file save it as a .sql file then run it from that somehow.... any thoughts?

IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Accreditation]') AND type in (N'U'))

BEGIN
CREATE TABLE [Accreditation]([ID] int, [PatientID] nvarchar(255), [DateOfService] datetime, [Study] nvarchar(255), [CaseStudy] bit, [Re-Scan] bit, [Re-Read] bit, [Correlation] bit, [Tech] nvarchar(100), [Physician] nvarchar(100), [Tech+] bit, [Phys+] bit, [upsize_ts] timestamp, )
END

ELSE

BEGIN
IF col_length('Accreditation','ID') is null
BEGIN
ALTER TABLE [Accreditation] ADD [ID] int
END

IF col_length('Accreditation','PatientID') is null
BEGIN
ALTER TABLE [Accreditation] ADD [PatientID] nvarchar(255)
END

IF col_length('Accreditation','DateOfService') is null
BEGIN
ALTER TABLE [Accreditation] ADD [DateOfService] datetime
END
DoCmd.RunSQL

mycon.Open "GET", VarStoring.SQLlocation
mycon.Send
mycon.waitforresponse
TableUpdate = mycon.responsetext

On Error Resume Next
DoCmd.RunSQL TableUpdate
On Error GoTo 0

[HiTechCoach]

The problem is that JET/ACE does not understand T-SQL syntax.

Using a pass-through query will send it directly to the in the SQL Server where the T-SQL needs to run.

[Movian]

Any resource showing me how to use pass-through querys in code ?

[orange]

Found this on Google. Hope it's helpful

http://www.dbforums.com/microsoft-access/1006185-writing-pass-through-queries-vba.html

I have used pass through queries with Oracle in the past. The sql in the query had to have the Oracle idiosyncrasies because the sql was processed by Oracle database not Access. May not apply to you, but just a caution in case...

[HiTechCoach]

Did you get it figured out?

Do you still need an example?

[Movian]

This does look like it will do what i need i just need to adjust my code to utilize the following function. Then modify the function to use the connection that has already been established as its a .adp

so i will be using currentproject.connection instead of a connection string. I am still technically on vacation but got a couple minutes to check on some things so will try and implement this as soon as i can. If anyone feels like throwing out an alteration to use the currentproject.connection instead of the connection string then please feel free never one to say no to help. But i don't want you to think im mooching ;) will try out a couple things as well.

Sub RunPassThrough(strSQL As String)

Dim qdfPassThrough As DAO.QueryDef, MyDB As Database
Dim strConnect As String

If Not IsNull(CurrentDb.QueryDefs("qrySQLPass").SQL) Then 'doesn't exist
CurrentDb.QueryDefs.Delete "qrySQLPass"
End If

Set MyDB = CurrentDb()

Set qdfPassThrough = MyDB.CreateQueryDef("qrySQLPass")

strConnect = "DRIVER=SQL Server;SERVER=Your_server_name;DATABASE=Your_datab ase_name;Uid=Your_userid;Pwd=Your_password;"

qdfPassThrough.Connect = "ODBC;" & strConnect
qdfPassThrough.SQL = strSQL
qdfPassThrough.ReturnsRecords = False
qdfPassThrough.Close

Application.RefreshDatabaseWindow

DoCmd.OpenQuery "qrySQLPass", acViewNormal, acReadOnly
DoCmd.Maximize

End Sub

Sub RunPassThrough(strSQL As String)

Dim qdfPassThrough As DAO.QueryDef, MyDB As Database
Dim strConnect As String

If Not IsNull(CurrentDb.QueryDefs("qrySQLPass").SQL) Then 'doesn't exist
CurrentDb.QueryDefs.Delete "qrySQLPass"
End If

Set MyDB = CurrentDb()

Set qdfPassThrough = MyDB.CreateQueryDef("qrySQLPass")

qdfPassThrough.Connect = currentproject.connection
qdfPassThrough.SQL = strSQL
qdfPassThrough.ReturnsRecords = False
qdfPassThrough.Close

Application.RefreshDatabaseWindow

DoCmd.OpenQuery "qrySQLPass", acViewNormal, acReadOnly
DoCmd.Maximize

End Sub

[hansup]

What is the meaning of a pass through query in an ADP? All ADP queries run in the SQL Server context.

[Movian]

Well it doesn't like somthing when i try to run my .sql file

as mentioned when my .adp uses the following to run a sql query it does not apear to update the tables in any way... no error message either... I will include an on error resume next around it in production. So i posted here to get some help and was told that it is to do with the T-SQL syntax and to use a pass through query so i did.... if i did not mention in my first post that i am using a .adp and that i have already established a connection to my SQL db then i apologize but that is the problem...

mycon.Open "GET", sqlFileWebAddress mycon.Send mycon.waitforresponse TableUpdate = mycon.responsetext DoCmd.RunSQL TableUpdate

[hansup]

This may be above my pay grade, but just wondering if you can do something like this:

Dim strSql As String
strSql = "CREATE PROC DoSomething AS " & mycon.responsetext
CurrentProject.Connection.Execute strSql
CurrentProject.Connection.Execute "DoSomething"