[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