[AccessD] Connect to SQL and retrieve records

Hi Darren:

To actually retrieve the data from the MS SQL server could be like this:

<code>
Public Function FillCompanies() As Boolean
    Dim objCmd As ADODB.Command
    
    On Error GoTo Err_FillCompanies
    
    FillCompanies = False
    
    Set objCmd = New ADODB.Command
    
    With objCmd
        .ActiveConnection = gstrConnection
        .CommandText = "REFillCompanies"
        .CommandType = adCmdStoredProc
    End With
    
    Set rsCompanies = New ADODB.Recordset
    rsCompanies.CursorLocation = adUseClient
    rsCompanies.Open objCmd, , adOpenStatic, adLockOptimistic
    
    With rsCompanies
        If .BOF = False Or .EOF = False Then .MoveLast
    End With
    
    Set objCmd = Nothing
    FillCompanies = True
    
    Exit Function
    
Err_FillCompanies:
    ShowErrMsg "FillCompanies"
    
End Function
</code>

Note this is using a MS SQL SP. The simple SP looks like this:

<code>
CREATE PROC REFillCompanies
AS
SELECT CasinoCompany.CompanyCode, CasinoCompany.CompanyName,
CasinoCompany.CompanyAbrev, CasinoCompany.Active AS CompanyActive,
CasinoLocations.LocationCode, CasinoLocations.LocationName,
CasinoLocations.Active AS LocationActive
 FROM CasinoCompany INNER JOIN CasinoLocations ON CasinoCompany.CompanyCode
= CasinoLocations.CompanyCode
 WHERE CasinoCompany.Active=1
 ORDER BY CasinoCompany.CompanyName, CasinoLocations.LocationName;

GO
</code>

I will send another piece of code that stores the retrieved recordset in a
list box.

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