Why is this producing an error?

[dvst8]

I am trying to access a database in VB, so I have tried the following statement:

Dim cnn As New ADODB.connection

Which gives the following error:

"Compile Error: User-defined type not defined"

This must be something very simple...but what?

I've successfully established database connection in ASP using: ccn = Server.CreateObject("ADODB.connection")
so I don't understand why the statement in question in invalid.

Thanks for any help!

[Yonatan]

Well, I don't work with databases, but if you say CreateObject works in ASP, why don't you try it in VB?

Dim cnn As Object
Set cnn = CreateObject("ADODB.connection")

[Frans C]

Yonatan's approach will probably work, but the object variable will be late bound, and thus slower.
To use:
Dim cnn As New ADODB.connection
you need to add a reference to the Microsoft ActiveX Data objects library. (Project --> References, check the checkbox and click OK)

Success

[dvst8]

i tried what you said frans, but i got the error:

"Name conflicts with existing, module or object library."

weird.

yonatan's solution's seems to have worked...although like you said, will not perform as well.

[Frans C]

If you also have a reference to DAo, this could be the problem. Both ADO and DAO have things like Recordset, connection and so on. If you use both, the types must be declared specific. So no more:
dim rs as recordset
but
dim rs as ado.recordset
or
dim rs as dao.recordset

[dvst8]

ok.. thanks frans.

i was wondering if someone might be able to spot what is wrong with the following piece of code (from the same program i am writing)

Private Sub find_and_replace(pattern As String, data As String)
    Selection.Find.ClearFormatting     **
    Selection.Find.Text = pattern
    Selection.Find.Execute Forward:=True
    Clipboard.Clear
    Clipboard.SetText (data)
    Selection.Paste
    Clipboard.Clear
End Sub


Private Sub report_button_Click()
    Set ObjWord = New Word.Application
    report_button.Enabled = False
    Set doc1 = ObjWord.Documents.Open("c:\temp\report.doc")
    Call find_and_replace("building", "M-2")

**gives me a runtime error 91: "Object Variable or with block variable not set"

This was working fine this morning..!! I don't know what I changed?!

[dvst8]

Sorry, I mis-copied the above code block, here is the whole thing, which gives me the same error that I mentioned above:

Private Sub find_and_replace(pattern As String, data As String)
    Selection.Find.ClearFormatting
    Selection.Find.Text = pattern
    Selection.Find.Execute Forward:=True
    Clipboard.Clear
    Clipboard.SetText (data)
    Selection.Paste
    Clipboard.Clear
End Sub


Private Sub report_button_Click()
    Set ObjWord = New Word.Application
    report_button.Enabled = False
    Set doc1 = ObjWord.Documents.Open("c:\temp\report.doc")
    Call find_and_replace("building", "M-2")
    
          
    stmSQL = "SELECT * FROM NRCAeroLabStaff WHERE FirstName = 'Steve'"
   
   Set oRS = CreateObject("ADODB.Recordset")
   
   ' Open the connection.
   oRS.Open stmSQL, "DSN=labdata"

   oRS.MoveFirst
   
   Do While Not oRS.EOF
   Selection.TypeText Text:=oRS("FirstName")
   Selection.TypeParagraph
   Selection.TypeText Text:=oRS("LastName")
   Selection.TypeParagraph
   oRS.MoveNext
   Loop

   'Close the Recordset object.
   oRS.Close
          
          
          
    doc1.SaveAs FileName:="c:\temp\report1.doc"
    doc1.Close

End Sub

can anyone see a mistake?

thanks!

[Frans C]

I think it has problems with the Selection object.
Try:
ObjWord.Selection.Find.ClearFormatting
etc.

[dvst8]

how do i define the ObjWord globally, when my program simply consists of two subroutines..?

I tried passing the ObjWord as an argument to the find_and_replace sub, but that didn't work...

[Frans C]

Did you pass it ByRef as Word.Application?

[dvst8]

yeah i think so....

i just deleted that sub()
it wasnt crucial. things seem to work ok now.
thanks for the help.

btw, would you happen to know the following:

once I have this VB program completely written, and saved as a standard .exe is there an easy way to run it from ASP? or do I have to change all the code to VBScript?