Printable View
Help! I have a flat file that I need to compare against to a table already populated in SQL6.5. I know how to open and read the file to an array on VB5.0 but I don't know how to connect to the SQL server and run a Qry to check each line.
Does any one have a snippet that shows me how to first connect to to the server, then run a simple query against a table.
If i were you, i would better use ActiveX Data Objects for running queries over SQL. What you shoul do is to add an DataEnviroment(DE) to your project and then a connection to your DE. On the first tab of the connection properties page you must choose the 'MS SQL Server ODBC' as provider and on the second tab, you can enter your servername, user id and pass and database name.
Then afterall all you have to do is to make a simple SQL Query like :
Dim SQLStmt As String
Dim MyRecSet As Recordset
SQLStmt = "SELECT col1, col2 FROM table WHERE column = '" + criteria +"'"
and execute it using the execute method of the connection.
Set MyRecSet = DE.Con.Execute (Query)
You can reach the results with a code sth. like this:
For i=1 to MyRecSet.RecordCount
Value1=MyRecSet.Fields(0)
MyRecSet.MoveNext
Next
I hope it is the answer to your question and will help.
..super! I've got DAO 2.0 referenced in. However, I need the name of the component that I should be using on the form to make those UID and SRV changes you mentioned. Have any idea what that may be iataman? Anybody?
O.K. I'm getting close. So far this what I was able to come up with
GLOBAL myFirstConnect as New Connection
Sub Connect()
myFirstConnect.Open
End Sub
Sub OpenMyFirstSQLTable()
Dim mtbl as New Recordset
mtbl.open "SELECT name FROM myFirstTable where Name = "Jimmy"
if mtbl.EOF = false
<fail safes here?>
end if
End Sub
I've gotten ADO (..not DAO as I had accidentally mentioned) 2.0 referenced into the scene but can't find the DataEnvironment(DE) component or object I need to enter the following parameters to simply use the
mtbl.open line.
Provider=??
Security=??
Persist Security Info=??
Initial Catalog=??
Data Source=??
Can some one direct me too this?