Running T_SQL script from Access (I can't get it right)

  • Thread starter Thread starter Bonno Hylkema
  • Start date Start date
B

Bonno Hylkema

It might be an old subject, Running T_SQL script froms Access, but I can't
get it right.

I am running Access 2003 and SQL Server 2000.

I generate a SQL script from Enterprise Manager through Generate SQL script,
options International Text (Unicode).

The script Table1.sql looks like follows:

CREATE TABLE [dbo].[Table1] (
[Field1] [int] NOT NULL ,
[Field2] [char] (50) COLLATE Latin1_General_CI_AS NULL ,
[Field3] [char] (50) COLLATE Latin1_General_CI_AS NULL
) ON [PRIMARY]
GO

In the Access adp I have a function that should read the contents of the
Table1.sql script file and execute the T_SQL command on SQL Server. The
function has the following code:

Const ForReading = 1, ForWriting = 2, ForAppending = 3
Const TristateUseDefault = -2, TristateTrue = -1, TristateFalse = 0
Dim fs
Dim f
Dim ts
Dim SQL As String
Dim cnn As ADODB.Connection

Set fs = CreateObject("Scripting.FileSystemObject")
Set f = fs.GetFile("C:\Temp\Table1.SQL")
Set ts = f.OpenAsTextStream(ForReading, TristateUseDefault)
SQL = ts.ReadAll
MsgBox SQL
ts.Close

Set cnn = CurrentProject.Connection
cnn.Execute SQL, , adCmdText
cnn.Close

When I run the code I get the error: Line 6: Incorrect syntax near 'GO'.

What is wrong with my setup? The MsgBox shows the proper commands, as far as
I can tell.

How should fs, f and ts be DIMmed?

Any help is welcome. Thanks in advance.

Bonno Hylkema
 
or you can pass the SQL file to OSQL.exe right??

-Aaron


Remove the GO, this is a batch command, not a T-SQL command.

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: http://cerbermail.com/?QugbLEWINF


Bonno Hylkema said:
It might be an old subject, Running T_SQL script froms Access, but I can't
get it right.

I am running Access 2003 and SQL Server 2000.

I generate a SQL script from Enterprise Manager through Generate SQL
script, options International Text (Unicode).

The script Table1.sql looks like follows:

CREATE TABLE [dbo].[Table1] (
[Field1] [int] NOT NULL ,
[Field2] [char] (50) COLLATE Latin1_General_CI_AS NULL ,
[Field3] [char] (50) COLLATE Latin1_General_CI_AS NULL
) ON [PRIMARY]
GO

In the Access adp I have a function that should read the contents of the
Table1.sql script file and execute the T_SQL command on SQL Server. The
function has the following code:

Const ForReading = 1, ForWriting = 2, ForAppending = 3
Const TristateUseDefault = -2, TristateTrue = -1, TristateFalse = 0
Dim fs
Dim f
Dim ts
Dim SQL As String
Dim cnn As ADODB.Connection

Set fs = CreateObject("Scripting.FileSystemObject")
Set f = fs.GetFile("C:\Temp\Table1.SQL")
Set ts = f.OpenAsTextStream(ForReading, TristateUseDefault)
SQL = ts.ReadAll
MsgBox SQL
ts.Close

Set cnn = CurrentProject.Connection
cnn.Execute SQL, , adCmdText
cnn.Close

When I run the code I get the error: Line 6: Incorrect syntax near 'GO'.

What is wrong with my setup? The MsgBox shows the proper commands, as far
as I can tell.

How should fs, f and ts be DIMmed?

Any help is welcome. Thanks in advance.

Bonno Hylkema
Click to expand...
Click to expand...
 
I was not familiar with the difference, but after some reading I now do
understand. GO is for Query Analyzer.

When you generate a SQL script from Enterprise Manager it will have multiple
GO's for each step in the script. Since I like to use the generated script
without manual updating, I came across an interesting post by Tibor Karaszi:

"Loop through the stuff that you want to execute, insert each row into a
variable until
you reach GO. Execute that variable. Continue looping. Don't send the GO."

This approach looks pretty good to me and I will try it out using VBA in
Access.

Do you have an idea about my second question: How should fs, f and ts be
DIMmed?

Many thanks for your reply.

Bonno Hylkema


Sylvain Lafontaine said:
Remove the GO, this is a batch command, not a T-SQL command.

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: http://cerbermail.com/?QugbLEWINF


Bonno Hylkema said:
It might be an old subject, Running T_SQL script froms Access, but I
can't get it right.

I am running Access 2003 and SQL Server 2000.

I generate a SQL script from Enterprise Manager through Generate SQL
script, options International Text (Unicode).

The script Table1.sql looks like follows:

CREATE TABLE [dbo].[Table1] (
[Field1] [int] NOT NULL ,
[Field2] [char] (50) COLLATE Latin1_General_CI_AS NULL ,
[Field3] [char] (50) COLLATE Latin1_General_CI_AS NULL
) ON [PRIMARY]
GO

In the Access adp I have a function that should read the contents of the
Table1.sql script file and execute the T_SQL command on SQL Server. The
function has the following code:

Const ForReading = 1, ForWriting = 2, ForAppending = 3
Const TristateUseDefault = -2, TristateTrue = -1, TristateFalse = 0
Dim fs
Dim f
Dim ts
Dim SQL As String
Dim cnn As ADODB.Connection

Set fs = CreateObject("Scripting.FileSystemObject")
Set f = fs.GetFile("C:\Temp\Table1.SQL")
Set ts = f.OpenAsTextStream(ForReading, TristateUseDefault)
SQL = ts.ReadAll
MsgBox SQL
ts.Close

Set cnn = CurrentProject.Connection
cnn.Execute SQL, , adCmdText
cnn.Close

When I run the code I get the error: Line 6: Incorrect syntax near 'GO'.

What is wrong with my setup? The MsgBox shows the proper commands, as far
as I can tell.

How should fs, f and ts be DIMmed?

Any help is welcome. Thanks in advance.

Bonno Hylkema
Click to expand...
Click to expand...
 
That might be a solution. But OSQL depends on ODBC if I am right. The
database I am interested in is on a production server, so the accessibility
is limited. The maintenance of the database is outsourced, so even the
smallest update is more an administrative task than a development task.

I have access to the production server through an Access connection, but no
RDP (remote desktop) and most likely no ODBC. I will check it out.

Many thanks for your reply.

Bonno Hylkema

or you can pass the SQL file to OSQL.exe right??

-Aaron


Remove the GO, this is a batch command, not a T-SQL command.

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: http://cerbermail.com/?QugbLEWINF


Bonno Hylkema said:
It might be an old subject, Running T_SQL script froms Access, but I
can't
get it right.

I am running Access 2003 and SQL Server 2000.

I generate a SQL script from Enterprise Manager through Generate SQL
script, options International Text (Unicode).

The script Table1.sql looks like follows:

CREATE TABLE [dbo].[Table1] (
[Field1] [int] NOT NULL ,
[Field2] [char] (50) COLLATE Latin1_General_CI_AS NULL ,
[Field3] [char] (50) COLLATE Latin1_General_CI_AS NULL
) ON [PRIMARY]
GO

In the Access adp I have a function that should read the contents of
the
Table1.sql script file and execute the T_SQL command on SQL Server. The
function has the following code:

Const ForReading = 1, ForWriting = 2, ForAppending = 3
Const TristateUseDefault = -2, TristateTrue = -1, TristateFalse = 0
Dim fs
Dim f
Dim ts
Dim SQL As String
Dim cnn As ADODB.Connection

Set fs = CreateObject("Scripting.FileSystemObject")
Set f = fs.GetFile("C:\Temp\Table1.SQL")
Set ts = f.OpenAsTextStream(ForReading, TristateUseDefault)
SQL = ts.ReadAll
MsgBox SQL
ts.Close

Set cnn = CurrentProject.Connection
cnn.Execute SQL, , adCmdText
cnn.Close

When I run the code I get the error: Line 6: Incorrect syntax near
'GO'.

What is wrong with my setup? The MsgBox shows the proper commands, as
far
as I can tell.

How should fs, f and ts be DIMmed?

Any help is welcome. Thanks in advance.

Bonno Hylkema
Click to expand...
Click to expand...
Click to expand...
 
You using Early Binding or Late Binding? In other words, did you go into
Tools | References and set a reference to FSO, or not?

If you did, then you'd want

Dim fs As FileScriptingObject
Dim f As File
Dim ts As TextStream

If not, then

Dim fs As Object
Dim f As Object
Dim ts As Object

Early Binding should be very slightly faster, and has the advantage that
you're get IntelliType when you're coding. Late Binding is probably better,
though, as you won't have to worry about versioning issues if your users
have a different version of the library installed than you do.

Hold on: it's obvious from your code that you don't have a reference set, or
else you wouldn't be defining what are intrinsic constants in the library.
That's the other advantage of Early Binding. The usual approach is to set a
reference, do you coding until it works properly, then define the constants
you need and remove the reference.
 
I thought that OSQL and ISQL were pre-ODBC or something ridiculous..
not PRE per se.. but what's it called again?

it's written about in 'Hitchhikers Guide to SQL Server and Visual
Basic' or something ridiculous like that

-Aaron


Bonno said:
That might be a solution. But OSQL depends on ODBC if I am right. The
database I am interested in is on a production server, so the accessibility
is limited. The maintenance of the database is outsourced, so even the
smallest update is more an administrative task than a development task.

I have access to the production server through an Access connection, but no
RDP (remote desktop) and most likely no ODBC. I will check it out.

Many thanks for your reply.

Bonno Hylkema

or you can pass the SQL file to OSQL.exe right??

-Aaron


Remove the GO, this is a batch command, not a T-SQL command.

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: http://cerbermail.com/?QugbLEWINF


It might be an old subject, Running T_SQL script froms Access, but I
can't
get it right.

I am running Access 2003 and SQL Server 2000.

I generate a SQL script from Enterprise Manager through Generate SQL
script, options International Text (Unicode).

The script Table1.sql looks like follows:

CREATE TABLE [dbo].[Table1] (
[Field1] [int] NOT NULL ,
[Field2] [char] (50) COLLATE Latin1_General_CI_AS NULL ,
[Field3] [char] (50) COLLATE Latin1_General_CI_AS NULL
) ON [PRIMARY]
GO

In the Access adp I have a function that should read the contents of
the
Table1.sql script file and execute the T_SQL command on SQL Server. The
function has the following code:

Const ForReading = 1, ForWriting = 2, ForAppending = 3
Const TristateUseDefault = -2, TristateTrue = -1, TristateFalse = 0
Dim fs
Dim f
Dim ts
Dim SQL As String
Dim cnn As ADODB.Connection

Set fs = CreateObject("Scripting.FileSystemObject")
Set f = fs.GetFile("C:\Temp\Table1.SQL")
Set ts = f.OpenAsTextStream(ForReading, TristateUseDefault)
SQL = ts.ReadAll
MsgBox SQL
ts.Close

Set cnn = CurrentProject.Connection
cnn.Execute SQL, , adCmdText
cnn.Close

When I run the code I get the error: Line 6: Incorrect syntax near
'GO'.

What is wrong with my setup? The MsgBox shows the proper commands, as
far
as I can tell.

How should fs, f and ts be DIMmed?

Any help is welcome. Thanks in advance.

Bonno Hylkema
Click to expand...
Click to expand...
Click to expand...
 
I'll try to find the reference this weekend I should be able to.. it's
not called named pipes.. something like 'SQLLIB' or something

-Aaron
ADP Nationalist


Bonno said:
That might be a solution. But OSQL depends on ODBC if I am right. The
database I am interested in is on a production server, so the accessibility
is limited. The maintenance of the database is outsourced, so even the
smallest update is more an administrative task than a development task.

I have access to the production server through an Access connection, but no
RDP (remote desktop) and most likely no ODBC. I will check it out.

Many thanks for your reply.

Bonno Hylkema

or you can pass the SQL file to OSQL.exe right??

-Aaron


Remove the GO, this is a batch command, not a T-SQL command.

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: http://cerbermail.com/?QugbLEWINF


It might be an old subject, Running T_SQL script froms Access, but I
can't
get it right.

I am running Access 2003 and SQL Server 2000.

I generate a SQL script from Enterprise Manager through Generate SQL
script, options International Text (Unicode).

The script Table1.sql looks like follows:

CREATE TABLE [dbo].[Table1] (
[Field1] [int] NOT NULL ,
[Field2] [char] (50) COLLATE Latin1_General_CI_AS NULL ,
[Field3] [char] (50) COLLATE Latin1_General_CI_AS NULL
) ON [PRIMARY]
GO

In the Access adp I have a function that should read the contents of
the
Table1.sql script file and execute the T_SQL command on SQL Server. The
function has the following code:

Const ForReading = 1, ForWriting = 2, ForAppending = 3
Const TristateUseDefault = -2, TristateTrue = -1, TristateFalse = 0
Dim fs
Dim f
Dim ts
Dim SQL As String
Dim cnn As ADODB.Connection

Set fs = CreateObject("Scripting.FileSystemObject")
Set f = fs.GetFile("C:\Temp\Table1.SQL")
Set ts = f.OpenAsTextStream(ForReading, TristateUseDefault)
SQL = ts.ReadAll
MsgBox SQL
ts.Close

Set cnn = CurrentProject.Connection
cnn.Execute SQL, , adCmdText
cnn.Close

When I run the code I get the error: Line 6: Incorrect syntax near
'GO'.

What is wrong with my setup? The MsgBox shows the proper commands, as
far
as I can tell.

How should fs, f and ts be DIMmed?

Any help is welcome. Thanks in advance.

Bonno Hylkema
Click to expand...
Click to expand...
Click to expand...
 
Many thanks you for your thorough explanation of binding and references. I
tried it out and it worked as advertised. Only finding the proper reference
"MS scripting runtime" took me a little while. I followed your tip without a
reference and it works like a charm.

Bonno Hylkema
 
Bonno Hylkema said:
It might be an old subject, Running T_SQL script froms Access, but I can't
get it right.

I am running Access 2003 and SQL Server 2000.

I generate a SQL script from Enterprise Manager through Generate SQL script,
options International Text (Unicode).

The script Table1.sql looks like follows:

CREATE TABLE [dbo].[Table1] (
[Field1] [int] NOT NULL ,
[Field2] [char] (50) COLLATE Latin1_General_CI_AS NULL ,
[Field3] [char] (50) COLLATE Latin1_General_CI_AS NULL
) ON [PRIMARY]
GO

In the Access adp I have a function that should read the contents of the
Table1.sql script file and execute the T_SQL command on SQL Server. The
function has the following code:

Const ForReading = 1, ForWriting = 2, ForAppending = 3
Const TristateUseDefault = -2, TristateTrue = -1, TristateFalse = 0
Dim fs
Dim f
Dim ts
Dim SQL As String
Dim cnn As ADODB.Connection

Set fs = CreateObject("Scripting.FileSystemObject")
Set f = fs.GetFile("C:\Temp\Table1.SQL")
Set ts = f.OpenAsTextStream(ForReading, TristateUseDefault)
SQL = ts.ReadAll
MsgBox SQL
ts.Close

Set cnn = CurrentProject.Connection
cnn.Execute SQL, , adCmdText
cnn.Close

When I run the code I get the error: Line 6: Incorrect syntax near 'GO'.

What is wrong with my setup? The MsgBox shows the proper commands, as far as
I can tell.

How should fs, f and ts be DIMmed?

Any help is welcome. Thanks in advance.

Bonno Hylkema


FORGETIT
Click to expand...
 
Back
Top