[AccessD] Connect to SQL and retrieve records

Hi Darren:

Here is a code sample that I would use to fill a list box:

<code>
Public Function FillCompanyList(ctlBox As Control, Id As Variant, row As
Variant, col As Variant, CODE As Variant) As Variant
    
'Common Combo and List box fill function.
'Assumes rsCasinoCompany recordset is the supplied data and has
'equal or more fields than required in the control.
    
On Error GoTo Err_FillCompanyList
    
Dim mvReturnVal As Variant
    
mvReturnVal = Null
    
With rsCompaniesResults
 Select Case CODE
  Case acLBInitialize                 ' Initialize.
   Set rsCompaniesResults = New ADODB.Recordset
   Set rsCompaniesResults = rsCompanies.Clone

   ' Or you can simply call the populating records like:
   ' set rsCompaniesResults = FillCompanies().clone given
   ' that the function is setup like so:
   ' Public Function FillCompanies() As Recordset...
   '   FillCompanies = rsCompanies
	             
   If .BOF = False Or .EOF = False Then
    .MoveFirst
    mvReturnVal = .RecordCount
   Else
    mvReturnVal = 0
   End If

   Case acLBOpen                  ' Open.
    mvReturnVal = Timer           ' Generate unique ID for control.
    gvComboTimer = mvReturnVal
   Case acLBGetRowCount           ' Get number of rows.
    mvReturnVal = .RecordCount
   Case acLBGetColumnCount        ' Get number of columns.
    mvReturnVal = ctlBox.ColumnCount
   Case acLBGetColumnWidth        ' Column width.
    mvReturnVal = -1              ' -1 forces use of default width.
   Case acLBGetFormat             ' Get format
    mvReturnVal = -1
   Case acLBGetValue              ' Get data.
    .MoveFirst
    .Move (row)
    mvReturnVal = .Fields(col)
  End Select
 End With
    
 FillCompanyList = mvReturnVal

Exit_FillCompanyList:
    Exit Function
    
Err_FillCompanyList:
    'Handles error situation caused an apparent unrelated error(s)
    'generated in other modules. (It loses its brains...)
    If Err.Number <> 91 Then
        ShowErrMsg "FillCompanyList"
    End If
    Resume Exit_FillCompanyList
    
End Function
</code>

Note: Using clone does not make another copy of the data but makes another
pointer to the data. Within the List box Row Source insert the name of the
function. I.e: FillCompanyList 

HTH
Jim
 
-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Darren D
Sent: Tuesday, October 30, 2007 6:35 PM
To: 'Access Developers discussion and problem solving'
Subject: Re: [AccessD] Connect to SQL and retrieve records

Hi Jim

Sorry - Wasn't clear

In an Access dB I would do something like the code below To get records

How is this achieved using the Connection strings to an SQL Server 2000 dB??

Many thanks

Darren

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    Dim db As DAO.Database
    Dim sel_SQL As String
    Dim rs As DAO.Recordset
    
    Set db = CurrentDb()
    sel_SQL1 = "SELECT * FROM Account"
    Set rs = db.OpenRecordset(sel_SQL)
    
    If (rs.EOF) Then
        MsgBox "NOTHING TO SHOW DUDE"
    Else
        While (Not (rs.EOF))
            Debug.Print !AccountNo
            rs.MoveNext
        Wend
    End If


    rs.Close
    Set rs = Nothing
    Set db = Nothing
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Darren D
Sent: Wednesday, 31 October 2007 11:47 AM
To: 'Access Developers discussion and problem solving'
Subject: Re: [AccessD] Connect to SQL and retrieve records

Hi Jim 

Thanks for this

I was after the bits after that - Where you set up a RS object then loop
through
the rs and build a string say of results and put them to a grid or populate
a
grid with the results 



Thanks in advance

Darren

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Jim Lawrence
Sent: Tuesday, 23 October 2007 9:31 PM
To: 'Access Developers discussion and problem solving'
Subject: Re: [AccessD] Connect to SQL and retrieve records

Hi Darren:

Once the MS SQL Database is setup it is really easy.

Public gstrConnection As String
Private mobjConn As ADODB.Connection

Public Function InitializeDB() As Boolean

On Error GoTo Err_InitializeDB

gstrConnection = "Provider=SQLOLEDB;Initial Catalog=MyDatabase;Data
Source=MyServer;Integrated Security=SSPI"

'Test connection string
Set mobjConn = New ADODB.Connection
mobjConn.ConnectionString = gstrConnection
mobjConn.Open
InitializeDB = True

Exit_InitializeDB:
    Exit Function

Err_InitializeDB:
InitializeDB = False

End Function

HTH
Jim

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Darren D
Sent: Monday, October 22, 2007 7:35 PM
To: 'Access Developers discussion and problem solving'
Subject: [AccessD] Connect to SQL and retrieve records

Hi All

 

>From an Access 200/2003 MdB

 

Does anyone have an example or some sample code where I can connect to an
SQL
Server dB

 

Perform a select on a table in the SQL dB

 

Return the records

 

Display them on some form or grid 

 

Then close the connections?

 

Many thanks in advance

 

DD

-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com

-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com

-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com

-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com