ADO for SQL - ADO for Access ? 
Author Message
 ADO for SQL - ADO for Access ?

This script taken from book 'WSH in 21 Days', uses ADO to connect to
SQL. I do not have SQL, only Access. I have set up the connection
string under ODBC in Control Panel to point to the Access database and
it works fine using other scripts. I have  substituted in the script
below, i.e
***********
'Create a connection string

sConnect = "DSN=Contact"
*************

However, line 37:

*************
Set rsetTables = connDb.OpenSchema(adSchemaTables)
***********

generates an error message saying this method is not supported by the
provider.

Is the problem with Access, or the script itself ? Could somebody look
at the script and suggest where I the error lies, and what arguments
must be passed from the command line to an Access database.

ColinR

********************************

Const adShemaTables = 20

If WScript.Arguments.Count <> 2 Then
 WScript.Echo "Usage: connect2.vbs [driver] [server] [user]" & _
 "[password] [database]"
 WScript.Echo ""
 WScript.Echo "    Connects to  database and displays information
about"
 WScript.Echo "    the tables within the database."
 WScript.Quit 0
End If

Dim sDriver, sServer, sUser,sPassword, sDatabase
Dim sConnect,connDb,rsetTables,sTablesListing,oShell

'Get parameters for connection
sDriver =WScript.Arguments(0)
sServer= WScript.Arguments(1)
sUser = WScript.Arguments(2)
sPassword = WScript.Arguments(3)
sDatabase = WScript.Arguments(1)

'Create a connection string

sConnect = "DSN=Contact"

Set connDb = WScript.CreateObject("ADODB.Connection")
connDb.ConnectionString = sConnect
connDb.Open

If Err.Number <> 0 Then
  ShowErr "Unable to connect to database."
End If
WScript.Echo "Connected to '" + sDatabase + "' on '" + sServer + "'"

'Use the connection schema to get the schema table

Set rsetTables = connDb.OpenSchema(adSchemaTables)
If Err.Number <> 0 Then
 ShowErr "Unable to open the schema."
End If

'Now retrieve and dump the table listing

sTablesListing = ""
Do While Not rsetTables.EOF
 sTablesListing = sTablesListing & rsetTables.Fields(2) & _
 Chr(10) & Chr(13)
If Err.Number <> 0 Then ShowErr "An error occured."
rsetTables.MoveNext
Loop

Set oShell = WScript.CreateObject("WScript.Shell")
oShell.Popup sTablesListing, 0, "Table listing from '" & sDatabase &
"'"
WScript.Quit 0

' Sub displays information about the current error then quits

Sub ShowErr(sDesc)
 WScript.Echo "Error: " & sDesc
 WScript.Echo "Err# " & Err.Number & " - " & Err.Description
 WScript.Quit Err.Number
End Sub

***********************'

--
Kind Regards

Colin Reed




Thu, 05 Dec 2002 03:00:00 GMT  
 ADO for SQL - ADO for Access ?
It seems to me that you can't open any form of schema for a non-SQL
database. At least thats how my experiments came out.

Good Luck Anyway

Dominic



Thu, 05 Dec 2002 03:00:00 GMT  
 ADO for SQL - ADO for Access ?
I just found this:

-">
A schema is a collection of database objects that are owned or have been
created by a particular user. In Microsoft SQL Server and ODBC 2.x terms, a
schema is an owner; there is no equivalent to a schema in a Microsoft Access
database.
<"-

Dominic



Thu, 05 Dec 2002 03:00:00 GMT  
 ADO for SQL - ADO for Access ?
I only started buying books relatively recently, (before that I just taught
myself). However I have found that the books are very helpful in general.

Especially on languages that are more object oriented than script and are
more involved in theory.

Good Luck anyway!

--
Dominic



Thu, 05 Dec 2002 03:00:00 GMT  
 ADO for SQL - ADO for Access ?
Thanks Dominic.

At least it put me out of my misery. It certainly would have been more
democratic had the authors included at least a couple of ADO scripts
for Access. Wrox's 'Beginning ASP' does just this. I am beginning to
think that buying a book to learn scripting/programming is a very hard
way to go about it. I am indebted to the patience of this group.

Regards

ColinR



Quote:
> I just found this:

> -">
> A schema is a collection of database objects that are owned or have
been
> created by a particular user. In Microsoft SQL Server and ODBC 2.x
terms, a
> schema is an owner; there is no equivalent to a schema in a
Microsoft Access
> database.
> <"-

> Dominic



Fri, 06 Dec 2002 03:00:00 GMT  
 
 [ 5 post ] 

 Relevant Pages 

1. Access on a MS-Access-database with an ADO-Object in Jscript

2. VBscript, ADO on windows 2000 is faster than C++ ATL, ADO

3. Problem seeking for records with ADO and SQL Server, Please Help

4. Connecting to SQL Server via VBScript from Outlook using ADO

5. Running SQL in ADO

6. Using ADO/stream to upload image into SQL

7. SQL Server7, ADO and UNICODE

8. Scrolling thru an ADO SQL recordset in ASP?

9. sql ado

10. Missing info : Using a SQL DB with ADO

11. SQL Server - IIS - ADO - ASP

12. ADO & SQL Question

 

 
Powered by phpBB® Forum Software