what's wrong with this code? 1

[torturedmind]

good day peopl. am not really a vb programmer tho i understand a bit so please bear with me.

i have the following code i copied from the faq section and modified it a little to my need. problem is it throws me an error on the line indicated which states "Compile error. Invalid use of New keyword."

Private Sub btn_inv_chk_Click()
    Dim mydata As DataObject
    Set mydata = New DataObject

    [COLOR=red][b]' The following line is where the error shows[/b][/color]
    Set db_data = New Connection

    db_data.CursorLocation = adUseClient

    [COLOR=green]' set up DSN-less connection
    ' db_data.Open "Driver={Microsoft dBASE Driver (*.dbf)};" & _
    '    "DriverID=277;" & _
    '    "Dbq=c:\windows\desktop"[/color]

    db_data.Open "Driver={Microsoft Visual FoxPro Driver};" & _
        "SourceType=DBF;" & _
        "SourceDB=c:\windows\desktop;" & _
        "Exclusive=No"

    Set Ado_data = New Recordset

    [COLOR=green]]' Note exactly the same select statement as would be used in a relational database
    '[/color]
    Ado_data.Open "select MIS.matr_no, MIS.qty from MIS.dbf MIS", _
        adOpenStatic, adLockOptimistic

    If Ado_data.RecordCount < 1 Then
        MsgBox "No data found!"
        Exit Sub
    End If

    [COLOR=green]' go to the first record in the set and loop around till no more available
    '[/color]
    Ado_data.MoveFirst
    For i = 0 To Ado_data.RecordCount - 1
        ' MsgBox (Ado_data.Fields.Item(0).Value & " " & Ado_data.Fields.Item(1).Value)
        db_block = db_block & Ado_data.Fields(0).Value & vbTab & Ado_data.Fields(1).Value & vbCrLf
        Ado_data.MoveNext
    Next

    mydata.SetText Mid(data_block, 1)
    mydata.PutInClipboard
    Cells(1, 1).Select
    ActiveSheet.Paste

    Ado_data.Close
    db_data.Close
End Sub

kilroy
philippines

"Once a king, always a king. But being a knight is more than enough."

 

[genomon]

You need to dim the variable as a connection object before you can set it (I think)

The Microsoft mascot is a butterfly.
A butterfly is a bug.
Think about it....

 

[PHV]

Have you referenced the Microsoft ActiveX Data Objects 2.x Library ?
Dim db_data As New ADODB.Connection

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[rmikesmith]

After toying around with this for a while, including settting/unsetting references, declaring variables in certain ways, and being unable to duplicate the compiler error, I searched MSDN. The only result returned was this KB article (and an identical one for XL2002):

http://support.microsoft.com/default.aspx?scid=kb;en-us;225059

You may want to check this out and see if it applies.


Regards,
Mike

 

[torturedmind]

thank you all for the suggestions. sir mike, your link was really helpful as it did solved the compiler error message. just after that workaround, another error popped up from the ffg. line of code:

    Ado_data.Open "select MIS.matr_no, MIS.qty from MIS.dbf MIS", _
        adOpenStatic, adLockOptimistic

any ideas?

kilroy
philippines

"Once a king, always a king. But being a knight is more than enough."

 

[rmikesmith]

You need to include the Connection object in the Recordset Open method:

Ado_data.Open "select MIS.matr_no, MIS.qty from MIS.dbf MIS", _
        [b]db_data[/b], adOpenStatic, adLockOptimistic

Mike

 

[torturedmind]

ei thatnks sir mike. you've been very very helpful indeed. for that i give a a star.

peace!

kilroy
philippines

"Once a king, always a king. But being a knight is more than enough."