connect to database Module ...Problem

[mot1639]

Hi All

In the module i write this

Public Sub test()
Dim con1 As New adodb.Connection
con1.CursorLocation = adUseClient
con1.ConnectionString = "Provider=MSDASQL.1;Persist Security Info=False;Data Source=test"
con1.Open
Dim k As New adodb.Recordset
Set k = New adodb.Recordset
k.Open "select * from table3", con1, adOpenDynamic, adLockOptimistic
Set Form1.DataGrid1.DataSource = k
End Sub



Public Sub count()
Dim con1 As New adodb.Connection
con1.CursorLocation = adUseClient
con1.ConnectionString = "Provider=MSDASQL.1;Persist Security Info=False;Data Source=test"
con1.Open
Dim k As New adodb.Recordset
Set k = New adodb.Recordset
k.Open "select * from table3", con1, adOpenDynamic, adLockOptimistic
MsgBox k.RecordCount
End Sub

in the form1

Private Sub Form_Load()
test
End Sub

Private Sub Command1_Click()
count
End Sub

the form_load work and the module fill the datagrid1 with the data

but when i click command1 to count the record i receive this error

"Compile Error: Function or Interface Marked as Restricted, or the Function
Uses an Automation Type Not Supported in Visual Basic"

when i move the code in count() to form1.. it work
please help me out in this problem

regards for all

[louvelle]

In the public sub count, try removing the first four lines.

[mot1639]

still same problem .. i remove :
Dim con1 As New adodb.Connection
con1.CursorLocation = adUseClient
con1.ConnectionString = "Provider=MSDASQL.1;Persist Security Info=False;Data Source=test"
con1.Open

?? & thanks in advance

[louvelle]

Hmm...which code does it highlight when the error exists?..

[mot1639]

the count in form1. !!

[louvelle]

*ahem..

*ahem.. you don't need to put an exclamation point at your sentence.
There are two words with a "count" on it so I don't know which is which..

If you mean by the count in the "msgbox k.recordcount", then just try this code:

Public Sub Count()
   Dim k as New ADODB.Recordset
   Set k = New ADODB.Recordset
   k.Open "SELECT COUNT(column_name) AS items FROM table_name", con1, 1, 2
   msgbox rs!items
End Sub

This SQL statement displays the number of items in the table.

[si_the_geek]

Change the name of the sub from Count to something else (perhaps CountTable3Records).

The error is implying that there is a hidden built-in routine/property/etc called Count, which is causing VB to get confused. Using a "proper" name avoids that kind of issue.


Note that there are a multitude of other smaller problems with your code, which can be corrected like this:

Public Sub CountTable3Records()
Dim con1 As adodb.Connection
Set con1 = New adodb.Connection
con1.ConnectionString = "Provider=MSDASQL.1;Persist Security Info=False;Data Source=test"
con1.Open
Dim k As adodb.Recordset
Set k = New adodb.Recordset
k.Open "select Count(*) from table3", con1, adOpenForwardOnly, adLockReadOnly, adCmdText
MsgBox k.Fields(0).Value
k.close
Set k = Nothing
con1.Close
Set con1 = Nothing
End Sub

The first change is explained by the article Why shouldn't I use "Dim .. As New .."? from our Classic VB FAQs (in the FAQ forum)

The .CursorLocation should not be set to Client side unless needed (such as for a DataGrid), because it slows things down and causes various issues with locking etc.

The next change is to close things when you have finished with them, otherwise you waste memory etc and cause database problems such as locking/corruption/etc.

There is no need to copy all data from the table into memory just to get a record count, instead simply ask the database to give you the count.

Using the right parameters for recordset.Open improves speed and more. For more information, see the article What do the parameters of the recordset.Open method mean? from our Database Development FAQs/Tutorials (at the top of the Database Development forum)

k.Open "SELECT COUNT(column_name) AS items FROM table_name", con1, 1, 2

Using "magic numbers" like that is a bad idea, because it makes the code much harder to read/write, and therefore much more likely to have bugs/errors.

Typing the full enum name (such as adOpenForwardOnly) isn't that hard, because you only need to type the first few letters and press Ctrl-Space.

[louvelle]

@Si

I see.. I usually use the full enum name a couple of years ago. But our instructor told us it's just the same as the full enum name if we use numbers.

Thanks for the info!

[si_the_geek]

It does have the same effect when it runs (assuming you use the right numbers!), but I've lost count of the amount of times I've seen threads where using numbers causes problems - usually because somebody copied code from somewhere else in their program, but the numbers weren't right for the new code.

[hope flower]

Thank Mr.si_the_geek , You are the man ...^^ and thank u Mis louvelle...
thank you really for your advice ,,, it work now
sorry ,, but what the best site that it give a sql command ?
and see the code below how i can make the single code count two table or more ??
i write this one but it give me an error for the second count ?

Dim con1 As adodb.Connection
Set con1 = New adodb.Connection
con1.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Documents and Settings\Owner\My Documents\Database2.mdb;Persist Security Info=False"
con1.Open
Dim k As adodb.Recordset
Set k = New adodb.Recordset
k.Open "select Count(*) from table1", con1, adOpenForwardOnly, adLockReadOnly, adCmdText
MsgBox k.Fields(0).Value
k.Open "select Count(*) from table2", con1, adOpenForwardOnly, adLockReadOnly, adCmdText
MsgBox k.Fields(0).Value
k.Close
Set k = Nothing
con1.Close
Set con1 = Nothing

[si_the_geek]

In future please don't just say "an error", tell us what the error is, and which line of code it occurred on. In this case my educated guess is probably right, but guessing takes extra time, and can be wrong.

You are getting an error because you aren't closing the recordset before you re-open it, eg:

k.Open "select Count(*) from table1", con1, adOpenForwardOnly, adLockReadOnly, adCmdText
MsgBox k.Fields(0).Value
k.Close
k.Open "select Count(*) from table2", con1, adOpenForwardOnly, adLockReadOnly, adCmdText
MsgBox k.Fields(0).Value
k.Close