Client/SQL Server Application Developement 101
Howdy,
I have to concur with JHausmann. Access is good front end for first time development. Provided that your OS, SQL and Microsoft Access lisencing is squared away, you should have no difficulties scripting apps that connect up to the server. Attached is a quick recipe for a simple app that will qry a table through a connection to SQL. I don't know where it originated but it certainly helped me get off the ground
I also agree the MSSQLw (Microsoft Query) rules the day and should not be used if you have minimal experience with it. In any case if you feel comfortable with the risks then the attached may get you started.
SQL SERVER STUFF
1. Using MSSQLw create a db on the server and called it "myTestdb"
2. Using MSSQLw create a table in the db called "Employees"
3. Using MSSQLw create the following fields "Name" and "time_on_job"
4. Using MSSQLw Populate 10 records worth of data
Example:
Ted, 20
Fred, 5
Missy, 6
Charles, 3
Angel, 1
Mark, 12
Joseph, 9
Katrina, 10
Peter, 8
George, 120
CLIENT WORKSTATION STUFF
4. Go to your win95 (works on any OS) machine and fire up Visual Basic
5. Start new project call it myFirstqry
6. On a the blank form create a button.
7. Append the following line code to the button_click sub
"meTheClient"
8. Now paste the following below the sub.
NOTE: You do not require a mapped network connection directly to the SQL server (i.e. S://SQLDEV001) for this to work. In this case the Database connection is established through ODBC. Your machine however must be a member of the same LAN or WAN the SQL server is on. You will require the signon and password to the table on the server for a successful connection.
Sub meTheClient()
Dim mydbsCurrent As Database
'NOTE: Make sure you have included 'Microsoft Access 9.0 Object Library' (or facsimile thereof)
'in order for the above Dim to work
Dim qdfJustPassingThrough As QueryDef
Dim qdfClient As QueryDef
Dim rstTopFive As Recordset
Dim strMessage As String
'You'll need a db for the query to dump all of its findings
Set mydbsCurrent = OpenDatabase("c:\temp\experiment001.mdb")
' The pass-through query to retrieve data from
' a Microsoft SQL Server database.
Set qdfJustPassingThrough = _
mydbsCurrent.CreateQueryDef("AllEmployees")
qdfJustPassingThrough.Connect = _
"ODBC;DATABASE=myTestdb;UID=sysop;PWD=wow;DSN=smalltables
qdfJustPassingThrough.SQL = "SELECT * FROM Allemployees
"ORDER BY time_on_job DESC"
qdfJustPassingThrough.ReturnsRecords = True
' Create a temporary QueryDef object to retrieve
' data from the pass-through query.
Set qdfClient = mydbsCurrent.CreateQueryDef("")
qdfClient.SQL = "SELECT TOP 5 Employees FROM AllEmployees"
Set rstFirstFive = qdfClient.OpenRecordset()
' Display results of queries.
With rstFirstFive
strMessage = _
"Our first 5 employees were:" & vbCr
Do While Not .EOF
strMessage = strMessage & " " & !Employee & _
vbCr
.MoveNext
Loop
'In the event you have multiple records with the same Index
If .RecordCount > 5 Then
strMessage = strMessage & _
"(There was a tie, resulting in " & _
vbCr & .RecordCount & _
" employees in the list.)"
End If
MsgBox strMessage
.Close
End With
'Delete the query once your done with it. Then again, you may want to comment
'out the last line keep it for bragging rights.
mydbsCurrent.QueryDefs.Delete "AllEmployees"
mydbsCurrent.Close
End Sub
9. Save; Run; cross your fingers and click the button
10. Guess what? You've just built your first crude (..very, since it only has a qry method) but functioning Client/SQL server app.
