Can someone test this vbscript? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Import/Export (DTS) and Replication (2000)

Can someone test this vbscript?

Author

Topic

Zathras
Starting Member

28 Posts

Posted - 2004-06-24 : 14:01:46

I'm having a real problem with this script. I am trying to get a list of all the tables in a MDB file, but can't get it to work. If I run this code in VB6 it works perfectly, but if I try to execute just this step in a DTS package, I get "Object or provider is not capable of performing requested operation" on the OpenSchema line. If you can test this, all you need is a data.mdb file on your C drive. Can someone try this in a DTS step and tell me if it works for you? Or maybe why it isn't working for me?


Function Main()
  Dim con, rec
  Dim strSQL, strFile
  Set con = CreateObject("ADODB.Connection")
  Set rec = CreateObject("ADODB.Recordset")

  strFile = "C:\data.mdb"
  strSQL = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + strFile
  con.Open strSQL
  
  Set rec = con.OpenSchema(adSchemaTables, Array(Empty, Empty, Empty, "Table"))
  Do While Not rec.EOF
      MsgBox rec.Fields("TABLE_NAME").Value
      rec.MoveNext
  Loop
End Function

timmy
Master Smack Fu Yak Hacker

1242 Posts

Posted - 2004-06-24 : 19:34:35

Yep - bombs on me too, with the same error.

Just a thought: In VB you explicitly reference the ADO dll. In this you don't - maybe it's picking up an older/incorrect version of the ADODB objects.

As an aside - have you tried querying the MSysObjects system table in Access? This will give you a list of tables.

Set rec = con.Execute ("Select Name FROM MSYSObjects WHERE Type = 1")

HTH,

Tim

Zathras
Starting Member

28 Posts

Posted - 2004-06-25 : 15:47:37

Your reply made me think.... yes, I was referencing the adodb object when I tried it in VB6. So then I removed the reference and ran it again. It gave an error becasue adSchemaTables was undefined. That is the error in the DTS package step! So instead of giving me an easily understandable error message, it gives that rather odd one in theis situation.

So I changed adSchemaTables to 20 in the vbscript, and the DTS runs fine now. I'll have to remember things like this in the future.

Thanks for your help!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources