quick SQL question

[NigelShaw]

Hi,

a quick SQL question. i have a code and everything set up to look through my SQL server and collect fields. the code is -

SELECT     fldCellPhone, fldFirstName, fldLastName, fldTotalVisits, fldLastVisit, fldFirstEmployeeName
FROM         tblClients
WHERE     (fldTotalVisits = 1) AND (fldFirstEmployeeName = 'employee' ) AND (fldLastVisit < CONVERT(DATETIME, '2010-05-20 00:00:00', 102))

from access, i need to create a table if a table doesnt exist and add the records to it by looping through it. my current code is-

Sub SQLCollectData()

    '##set up your variables##
    Dim m_oRecordset As ADODB.Recordset
    Dim m_sConnStr As String
    Dim sSQL As String
    Dim oConnection1 As ADODB.Connection
    Dim strSource As String
    Dim strSourceEnviron As String
    Dim strSourceServer As String
    Dim strCatalog As String
    Dim strGetData As String
    Dim strAddString As String
    Dim intGetNumber As Integer

    Dim db As DAO.Database
    Dim rss As DAO.Recordset
    Dim qdf As DAO.QueryDef


    '##collect environment data##
    strSourceEnviron = VBA.Environ("computername")
    strSourceServer = "\CMJ"
    strSource = strSourceEnviron & strSourceServer
    strCatalog = "SalonIris"

    '##create connection to SQL server##
    m_sConnStr = "Provider='SQLOLEDB';Data Source='" & strSource & "';" & _
    "Initial Catalog='" & strCatalog & "';Integrated Security='SSPI';"

    Set oConnection1 = New ADODB.Connection
    oConnection1.CursorLocation = adUseClient
    oConnection1.Open m_sConnStr

    '##Create specific SQL statement##
    'sSQL = "SELECT fldFirstName, fldLastName, fldActive " & _
    '"FROM tblEmployees WHERE fldActive = 1"

    '##Create specific SQL statement##
    sSQL = "SELECT fldFirstName, fldLastName, fldDateScheduled, fldCheckedIn, fldCNClosed " & _
    "FROM tblTicketsSummary WHERE fldCheckedIn <> 0"

    'WHERE fldDateScheduled = CONVERT(DATETIME, CONVERT(varChar, GetDate(), 101))" & _
    '" AND fldCheckedIn <> 0 AND fldCNClosed = 0"

    '##Run SQL##
    Set m_oRecordset = New ADODB.Recordset
    m_oRecordset.Open sSQL, oConnection1, adOpenStatic, _
    adLockBatchOptimistic, adCmdText

    m_oRecordset.MarshalOptions = adMarshalModifiedOnly

    Set m_oRecordset.ActiveConnection = Nothing

    '##count records for testing##
    With m_oRecordset
        j = .RecordCount

        '##Set up loop through records##
        For i = 1 To j

            '##collect data from table or query##

            strAddString = "@textmagic.co.uk"  'set up the mobile number for text alert
            strGetData = !fldFirstName
            strGetData = strGetData & " " & !fldLastName
            intGetNumber = !fldCellNumber

'###I NEED TO CHECK FOR A TABLE AND CREATE ONE IF IT DOESNT EXIST HERE THEN ADD THE DATA COLLECTED INTO THE FIELDS ###

            'MsgBox strGetData
            .MoveNext
        Next i

    End With

    '##clean up##
    m_oRecordset.Close
    oConnection1.Close

    Set m_oRecordset = Nothing
    Set oConnection1 = Nothing


    Exit Sub

Pretty desperate for once in my life LOL





any help greatly appreciated people



Nidge

 

[KeithG]

to check for a table you need to checkt he tabledef collection for the table.

 

[ByteMyzer]

Hello, NigelShaw,

To create a table in an SQL Server database and populate the data, see my post here.

Also, to determine whether a table exists in the SQL Server Database, try the following SQL syntax (substitute highlighted text with actual database/table names):

SELECT * FROM [B][I]MyDatabase[/I][/B].dbo.sysobjects
WHERE name = '[B][I]MyTable[/I][/B]'
AND type = 'U'

 

[NigelShaw]

Hi

Ok, ditched the make table option. i have a permanent table in the db now. my quest would be to collect the data from SQL Sqerver with my ADODB connection above and add the recordset to the table. How would i do that in ADO? would i create a DAO recordset to open and append tha table and loop through the ADO records adding them to the table or is there a much simpler way?

regs


Nigel

 

[ByteMyzer]

NigelShaw,

If both the source and target tables are in the SQL Server Database, why not simply execute a single Pass-Through Query?

Example:

oConnection1.Execute "INSERT INTO MyNewTableName (fldCellPhone, fldFirstName, fldLastName, fldTotalVisits, fldLastVisit, fldFirstEmployeeName)" _
    & " SELECT fldCellPhone, fldFirstName, fldLastName, fldTotalVisits, fldLastVisit, fldFirstEmployeeName" _
    & " FROM   tblClients" _
    & " WHERE  fldTotalVisits = 1" _
    & " AND    fldFirstEmployeeName = 'employee'" _
    & " AND    fldLastVisit < CONVERT(DATETIME, '2010-05-20 00:00:00', 102)"

 

[NigelShaw]

Yeah hi

The data is collected from SQL server but stored locally to access in a table. I originally wanted to display a query in in access with the data from SQL server but it seems an impossible task.

I have the data in SQL server and I need to collect info of people who only visited our shop once. The query I can do, it's storing and using the data I cannot do. My thought was to collect using the ADO connection and loop through each record while adding the record to the local table with DAO unless I can pass it with ADO

I'll try a sample and see how I get on.


N

 

[NigelShaw]

Hi

Thought I'd update what is become more of a blog than a thread :-D

Got it all working now so I can collect my SQL server data with ADO and pass it to a local file with DAO. All works a treat.

So to my next question.

The SQL string in my code collects data for one person only but I need to collate the list for numerous people and create a single table of data. I've tried using the AND statement but it doesn't do anything. What would be the best statement to do this?

Cheers


Nigel