Steps to Reproduce Behavior

1. Create a module and type the following line in the Declarations section if it is not already there:
   

         Option Explicit
   						

2. Type the following procedure:
   

         Function TestSQLData()
   
            Dim db As Database, rs As Recordset
            Dim idx, td
            Dim cmd As String
   
            ' Delete TestTable if it exists on the SQL server.
            Set db = OpenDatabase("", False, False,ODBC;dsn=<datasource>; _
               database=<database>;uid=<user id>;pwd=<password>")
            cmd = "if exists (select * from sysobjects where _
               id = object_id('dbo.TestTable'))"
            cmd = cmd & " drop table TestTable"
            db.Execute cmd, dbSQLPassThrough
   
            ' Create TestTable with one field on SQL server.
            Set td = db.CreateTableDef("TestTable")
            td.Fields.Append td.CreateField("Int", dbInteger)
            td.Fields.Append td.CreateField("String", dbText, 50)
            db.TableDefs.Append td
   
            Set idx = td.CreateIndex("MyIdx")
            idx.Unique = True
            idx.Fields.Append idx.CreateField("Int")
            td.Indexes.Append idx
   
            cmd = "create Default TestDef3 as 100"
            db.Execute cmd, dbSQLPassThrough
   
            cmd = "sp_bindefault TestDef3, 'TestTable.Int'"
            db.Execute cmd, dbSQLPassThrough
   
            ' Open table, add a record, and then obtain values.
            Set rs = db.OpenRecordset("TestTable")
            rs.AddNew
            rs!String = "Trial"
            rs.Update
   
            Debug.Print "RecordCount = " & rs.RecordCount
            rs.MoveFirst
            Debug.Print "String is " & rs("String")
            Debug.Print "Int is " & rs("Int")
            rs.Close
   
         End Function
   						

3. To test this function, type the following line in the Debug window, and then press ENTER:
   
   ? TestSQLData()
   
   Note that run-time error '3167' occurs.

Back to the top