SQL Connection - Where to go

[NigelShaw]

Hi everyone,

i have created a connection to an SQL database to add on and change data within the database structure as the specific program i use doesnt have the features i need. however, now the connection is created, im unsure on how to retrieve or update the tables. can anyone offer a bit of guidance please?

here is my connection-

Public Sub SQLConnect()
    On Error GoTo ErrorHandler

    Dim Cnxn1 As ADODB.Connection
    Dim strCnxn As String
    Dim strServer As String
    Dim strTrusted As String
    Dim strApp As String
    Dim strDatabase As String
    Dim strNetwork As String
    Dim strTable As String
    
    strServer = forms!frmMain!txtServerName
    strTrusted = forms!frmMain!txtTrust
    strApp = forms!frmMain!txtApplication
    strDatabase = forms!frmMain!txtDatabase
    strNetwork = forms!frmMain!txtNetwork
    strTable = "dbo.tblProductsAndServices"
    
    
    ' Open a connection using the Microsoft ODBC provided
    
    Set Cnxn1 = New ADODB.Connection
    Cnxn1.ConnectionString = "driver={SQL Server};" & _
    "server={" & strServer & "};" & _
    "Trusted_Connection={" & strTrusted & "};" & _
    "APP={" & strApp & "};" & _
    "DATABASE={" & strDatabase & "};" & _
    "NETWORK={" & strDatabase & "};" & _
    "TABLE={" & strTable & "};"


    Cnxn1.Open strCnxn
    Cnxn1.DefaultDatabase = strDatabase
    
    ' Display the provider to test
    MsgBox "Cnxn1 provider: " & Cnxn1.Provider

'Now the connection is open, i need to get or alter the data here i think!!
    
   

    ' clean up
    Cnxn1.Close
    
    Set Cnxn1 = Nothing
   
    Exit Sub
    
ErrorHandler:
    If Not Cnxn1 Is Nothing Then
        If Cnxn1.State = adStateOpen Then Cnxn1.Close
    End If
    Set Cnxn1 = Nothing
    
    If Err <> 0 Then
        MsgBox Err.Source & "-->" & Err.Description, , "Error"
    End If
    
End Sub

many thanks guys


Nidge

 

[LPurvis]

Wow that's a big open subject right there.
It's a bit like saying...

"First, the Earth cooled... <Complete:>"

Learning is best achieved with an objective - ideally a requirement. Nothing quite like a need to acquire info and make it stick.
If you've used DAO previously, then the basics of working with the ADO OM from that point on will have a good few areas of familiarity.
You'll open recordsets to read (or update) data, execute statements against the connection itself or command objects to perform insert, update or deletes.

You'll find oodles of examples of working with such recordsets and command objects - it'll help to find them with a narrowing of intent though.

Cheers.

 

[NigelShaw]

Hi Leigh

thanks for your reply, ill leave the story of evolution for another day lol...

i have used DAO often but actually didnt realise it could be used to connect to SQL server. i have added to my code as below-

Public Sub SQLConnect()
    On Error GoTo ErrorHandler

    Dim Cnxn1 As ADODB.Connection
    Dim rstEmployees As ADODB.Recordset
    Dim strCnxn As String
    Dim strSQLEmployees As String
    
    Dim strServer As String
    Dim strTrusted As String
    Dim strApp As String
    Dim strDatabase As String
    Dim strNetwork As String
    Dim strTable As String
    
    Dim strGetData As String
    
     strServer = forms!frmMain!txtServerName
    strTrusted = forms!frmMain!txtTrust
    strApp = forms!frmMain!txtApplication
    strDatabase = forms!frmMain!txtDatabase
    strNetwork = forms!frmMain!txtNetwork
    strTable = "dbo.tblProductsAndServices"
    
    
    ' Open a connection using the Microsoft ODBC provided
    
    Set Cnxn1 = New ADODB.Connection
    Cnxn1.ConnectionString = "driver={SQL Server};" & _
    "server={" & strServer & "};" & _
    "Trusted_Connection={" & strTrusted & "};" & _
    "APP={" & strApp & "};" & _
    "DATABASE={" & strDatabase & "};" & _
    "NETWORK={" & strDatabase & "};" '& _
    '"TABLE={" & strTable & "};"


    Cnxn1.Open strCnxn
    Cnxn1.DefaultDatabase = strDatabase
    
    ' Display the provider to test
    'MsgBox "Cnxn1 provider: " & Cnxn1.Provider
    
 
   [B] ' Open table
    Set rstEmployees = New ADODB.Recordset
    strSQLEmployees = "tblEmployees"
    rstEmployees.Open strSQLEmployees, Cnxn1, adOpenKeyset, adLockOptimistic, adCmdTable
    
    'Create a record count and loop through records
    j = rstEmployees.RecordCount
    For i = 1 To j
    
    strGetData = rstEmployees!fldFirstName
    strGetData = strGetData & " " & rstEmployees!fldLastName
    
    'show the record result
    MsgBox strGetData
    rstEmployees.MoveNext
    Next i[/B]
    
    


    ' clean up
    Cnxn1.Close
    
    Set Cnxn1 = Nothing
   
    Exit Sub
    
ErrorHandler:
    If Not Cnxn1 Is Nothing Then
        If Cnxn1.State = adStateOpen Then Cnxn1.Close
    End If
    Set Cnxn1 = Nothing
    
    If Err <> 0 Then
        MsgBox Err.Source & "-->" & Err.Description, , "Error"
    End If
    
End Sub

i have an objective. i need to get the recordset from a table of people where a "fldCheckedIn" field is True and the "fldScheduled" = the date for the current day.

i just need to figure out now how to integrate the sql statement into the above......
for any guru's willing to direct, here is my sql string

SELECT     fldFirstName, fldLastName, fldFirstEmployeeName, fldCheckedIn, fldScheduled, fldProcessed
 FROM       [tblClients]
WHERE     (fldScheduled = Date())
AND         fldCheckedIn = True
AND         fldProcessed = False

the idea is to retrieve a list of names that are scheduled in and checked in but not processed. Basically it should give me a list of people currently logged as in the building.

using DAO, i would probably used the sql string in either the OpenRecordset option or use RunSQL but cant seem to get this with ADO

is it something simple that i just dont see properly?


many thanks


Nidge

 

[NigelShaw]

hi

i dont quite where im going wrong here. ive highlighted the queried area in RED.
According to internet searches & MSDN, it should work but i get an error message saying "incorrect syntax near the word 'select'."

if i replace this

rstEmployees.Open (SQL), Cnxn1, adOpenKeyset, adLockOptimistic, adCmdTable

with this

rstEmployees.Open"tblEmployees", Cnxn1, adOpenKeyset, adLockOptimistic, adCmdTable

it works like a charm but i need the SQL to filter down on conditions

my code to date-

Public Sub SQLConnect()
    On Error GoTo ErrorHandler

    Dim Cnxn1 As ADODB.Connection
    Dim rstEmployees As ADODB.Recordset
    
    Dim strServer As String
    Dim strTrusted As String
    Dim strDatabase As String
    
    Dim strGetData As String
    Dim SQL As String
    
    
    '##SET UP THE VARIABLES##
    '##FINAL RESULT BEING FORM FIELDS NOT HARD CODE##
    
    strServer = "Nigels-Laptop-7\CMJ"
    strTrusted = "Yes"
    strDatabase = "SalonIris"
    
    
    '##SET UP THE ODBC CONNECTION##
    
    Set Cnxn1 = New ADODB.Connection
    
    Cnxn1.ConnectionString = "driver={SQL Server};" & _
    "server={" & strServer & "};" & _
    "Trusted_Connection={" & strTrusted & "};" & _
    "DATABASE={" & strDatabase & "};"

    Cnxn1.Open
    
    [COLOR="Red"]SQL = ("SELECT * FROM tblEmployees")
    
    ' Open table
    Set rstEmployees = New ADODB.Recordset
    rstEmployees.Open (SQL), Cnxn1, adOpenKeyset, adLockOptimistic, adCmdTable[/COLOR]
    
    j = rstEmployees.RecordCount
    
    For i = 1 To j
    
    strGetData = rstEmployees!fldFirstName
    strGetData = strGetData & " " & rstEmployees!fldLastName
    
    MsgBox strGetData
    rstEmployees.MoveNext
    Next i
    
    ' clean up
    Cnxn1.Close
    
    Set Cnxn1 = Nothing
   
    Exit Sub
    
ErrorHandler:
    If Not Cnxn1 Is Nothing Then
        If Cnxn1.State = adStateOpen Then Cnxn1.Close
    End If
    Set Cnxn1 = Nothing
    
    If Err <> 0 Then
        MsgBox Err.Source & "-->" & Err.Description, , "Error"
    End If
    
End Sub

help really appreciated


thanks

Nigel

 

[LPurvis]

Hi

Just opening a recordset based on that required SQL statement (allowing for T-SQL), something like:

strSQLEmployees = "SELECT fldFirstName, fldLastName, fldFirstEmployeeName, fldCheckedIn, fldScheduled, fldProcessed " & _
    "FROM tblClients " & _
    "WHERE fldScheduled = CONVERT(DateTime, CONVERT(varChar, GetDate(), 101)) " & _
    "  AND fldCheckedIn <> 0 AND fldProcessed = 0"
 
rstEmployees.Open strSQLEmployees, Cnxn1, adOpenKeyset, adLockOptimistic

Cheers.

 

[NigelShaw]

Hi Leigh

thanks for your reply. I'm going to test it later

what is T-SQL?

Looking at your code, am I right in saying you cannot use the * in that instance?

Cheers,


Nigel

 

[NigelShaw]

Hi

same error message

isnt there an easier way to run a SQL string?

Seems awfully difficult to me......




Nidge

 

[LPurvis]

T-SQL is SQL Server's dialect of SQL.
You're presumably also using Management Studio to administer and setup your database?
Can you run the statement directly there?

SELECT fldFirstName, fldLastName, fldFirstEmployeeName, fldCheckedIn, fldScheduled, fldProcessed
FROM tblClients
WHERE fldScheduled = CONVERT(DateTime, CONVERT(varChar, GetDate(), 101))
AND fldCheckedIn <> 0 AND fldProcessed = 0

Cheers

 

[ByteMyzer]

If you are opening a recordset on a select statement, and not directly on a table, your statement should be:

rstEmployees.Open (SQL), Cnxn1, adOpenKeyset, adLockOptimistic, [COLOR="Red"][B]adCmdText[/B][/COLOR]

 

[NigelShaw]

Hi guys

I've changed my code now to something slightly different as the codes above wasn't working. I can now execute a statement however cannot get it to filter with a True or False

WHERE fldActive = True

would I better to declare this and construct the string?

Dim blnActive As Boolean

"WHERE fldActive = " & blnActive

lastly

couldn't get the date convert to work either. I'm using the express SQL to execute and test the queries which work fine in the editor and display the correct results.


Thanks for your help so far


Nidge

 

[LPurvis]

Hi again

It's impossible to know exactly what you tried or what you're now using.
You're building the SQL up in code. There's plenty of opportunity for that to go wrong where you'd think you're using code as suggested, but something different is happening.

Bear in mind that you're using T-SQL only in the statement you're building now.
The Convert code should have worked. But in what way did it not work? (You need to be explicit).
Your boolean comparison will also fail - you need to be careful to use T-SQL syntax and there is no True constant in T-SQL.

When concatenated with a string, VBA will coerce True into a literal string of "True" instead of -1.
A bit fields stores 0 or 1 (or Null if it's permitted).

"WHERE fldActive = " & IIF(blnActive, 1, 0)

Cheers.

 

[ByteMyzer]

In your SQL statement,

For True:

WHERE fldActive = 1

For False:

WHERE fldActive = 0