vbs to sql-server

[Daedalus]

Hi
I've made an Access front-end using SQL-server tables. No prob so far. Now
I'd like to create some vbs-scripts, in order to do some updates to the
tables. Running from the Acces-db (vba) it works fine, after adding the
reference. However, from within vbs, it doesn't work at all. The coding goes
as far as line 2 :-(
Apparently, it can't deal with the adodb. I suppose I have to add some
declaration of the used library ? How can I solve this ?
Here is the coding :

Option Explicit
Dim cn as New ADODB.Connection, rs as New ADODB.Recordset, ctr

cn.Open ("Provider=SQLOLEDB;Data
Source=fdp01;database=fdp_db;trusted_connection=ye s")
Set rs = cn.Execute("select * from installations")
rs.MoveFirst
ctr = 0
Do While Not rs.EOF
ctr = ctr + 1
rs.MoveNext
Loop
msgbox "Total in table : " & ctr
Many tanx in advance !
Yours truly
D

[Lyle Fairfield]

"Daedalus" <FD********@hotmail.com> wrote in
news:C4***********************@phobos.telenet-ops.be:

Hi
I've made an Access front-end using SQL-server tables. No prob so far.
Now I'd like to create some vbs-scripts, in order to do some updates
to the tables. Running from the Acces-db (vba) it works fine, after
adding the reference. However, from within vbs, it doesn't work at
all. The coding goes as far as line 2 :-(
Apparently, it can't deal with the adodb. I suppose I have to add some
declaration of the used library ? How can I solve this ?
Here is the coding :

Option Explicit
Dim cn as New ADODB.Connection, rs as New ADODB.Recordset, ctr

cn.Open ("Provider=SQLOLEDB;Data
Source=fdp01;database=fdp_db;trusted_connection=ye s")
Set rs = cn.Execute("select * from installations")
rs.MoveFirst
ctr = 0
Do While Not rs.EOF
ctr = ctr + 1
rs.MoveNext
Loop
msgbox "Total in table : " & ctr
Many tanx in advance !
Yours truly
D

VBS does not Dim variables as specific types (unless it changed )

Generally we use something like:

Dim CN

Set CN = Server.CreateObject("ADODB.Connection")

or sometimes just

Set CN = CreateObject("ADODB.Connection")

[Eric Schittlipz]

"Daedalus" <FD********@hotmail.com> wrote in message
news:C4***********************@phobos.telenet-ops.be...

Hi
I've made an Access front-end using SQL-server tables. No prob so far. Now
I'd like to create some vbs-scripts, in order to do some updates to the
tables. Running from the Acces-db (vba) it works fine, after adding the
reference. However, from within vbs, it doesn't work at all. The coding goes as far as line 2 :-(
Apparently, it can't deal with the adodb. I suppose I have to add some
declaration of the used library ? How can I solve this ?
Here is the coding :

Option Explicit
Dim cn as New ADODB.Connection, rs as New ADODB.Recordset, ctr

cn.Open ("Provider=SQLOLEDB;Data
Source=fdp01;database=fdp_db;trusted_connection=ye s")
Set rs = cn.Execute("select * from installations")
rs.MoveFirst
ctr = 0
Do While Not rs.EOF
ctr = ctr + 1
rs.MoveNext
Loop
msgbox "Total in table : " & ctr
Many tanx in advance !
Yours truly
D

vbs is not strongly typed - so you cannot write:
Dim cn as New ADODB.Connection
Instead you will have to write something more like below. You would also
have to put in some error handling code, which is not easily done in vbs.
Is vbs the only choice you have?
Option Explicit

Dim strCnn
Dim strSQL
Dim cnn
Dim rst
Dim ctr

strCnn = "Provider=sqloledb;" & _
"Data Source=fdp01;" & _
"Initial Catalog=fdp_db;" & _
"Integrated Security=SSPI;"

Set cnn = CreateObject("ADODB.Connection")

cnn.ConnectionString = strCnn

cnn.Open

strSQL = "select * from installations"

Set rst = CreateObject("ADODB.Recordset")

rst.Open strSQL, cnn

ctr = 0

While Not rst.EOF
ctr = ctr + 1
rst.MoveNext
Wend

Msgbox ctr

rst.Close

Set rst = Nothing

cnn.Close

Set cnn = Nothing

[Daedalus]

Tanx guys, this exactly what I was looking 4 !
D