Darren D
darren at activebilling.com.au
Wed Oct 31 21:01:19 CDT 2007
Hi Jim
Brilliant - I am experimenting with this type of thing a lot and was going to
start asking questions about Stored Procedures - thank you very much
DD
-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Jim Lawrence
Sent: Thursday, 1 November 2007 8:01 AM
To: 'Access Developers discussion and problem solving'
Subject: Re: [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
--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com