ADODB read table 1

[vttech]

I am new to VBA

I have a form that has a command button called cmdSubmit

that one click I want to connect to a Database called "NCAA"
stored at the following location "\\serv1\db\"

within the NCAA database I have a table call tblPatientLanguage
that contains two colomns "PatientID" and "Language"

I need to display the contents stored in both "PatientID" and "Language" in a msgbox

I have gotten as far as this


Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset

Set cn = CurrentProject.Connection
Set rs = New ADODB.Recordset




Set rs = Nothing


any ideas on how to accomplish this task??

Newbie in search of knowledge

 

[JerryKlmns]

The following code defines some properties of the Recordset object, opens, loops all records despalyed with msgbox and closes it.

With rs
   .ActiveConnection = cn
   .CursorLocation = adUseServer
   .CursorType = adOpenStatic
   .LockType = adLockReadOnly
   .Source = "SELECT tblPatientLanguage.* FROM tblPatientLanguage"
   .Open
   While Not .EOF
       Msgbox  "PatientID :" & .Fields("PatientID") & " and Language :"  & .Fields("Language")
       .MoveNext
   Wend
   .Close
End With

There are other ways to open a recordset. Place the cursor whereever you have a question about and press F1--> Help files is a good place to start reading.

 

[PeteJohnston]

Is there a reason to set up an ADODB connection on-the-fly? Unless the same table could possibly be accessed in several different databases would it not be easier to set up tblPatientLanguage as a linked table? Then you could just do

Set rs = db.OpenRecordset("SELECT tblPatientLanguage.* FROM tblPatientLanguage"
With rs
  If .RecordCount > 0 Then
    .MoveFirst
    Do While Not .EOF
      ..... something
      .MoveNext
    Loop
  EndIF
End With

PeteJ
(Contract Code-monkey)

It's amazing how many ways there are to skin a cat
(apologies to the veggies)

 

[JerryKlmns]

Forgot the connection part...
Assuming access database. Commented lines handle user-level security

Set cn = New ADODB.Connection
With cn
  .Provider = "Microsoft.Jet.OLEDB.4.0"
  .Properties("Data Source") = "\\serv1\db\NCAA.mdb"
'  .Properties("Jet OLEDB:System database") = "\\serv1\db\NCAA.mdw"
'  .Properties("User ID") = TheUserID
'  .Properties("Password") = ThePassword
'  .Properties("Persist Security Info") = False
  .Properties("Mode") = adModeShareDenyNone
  .Open
End With

 

[vttech]

Why have "tblPatientLanguage.*" in the sql statemnt below?

.Source = "SELECT tblPatientLanguage.* FROM tblPatientLanguage"


does the statment below do the same thing

.Source = "SELECT * FROM tblPatientLanguage"



Newbie in search of knowledge

 

[JerryKlmns]

It does indeed and

.Source = "SELECT tblPatientLanguage.* FROM tblPatientLanguage"

could also be just

.Source = "SELECT PatientID, Language FROM tblPatientLanguage"

 

[vttech]

While Not .EOF
       MsgBox "PatientID :" & .Fields("PatientID") & " and Language :" & .Fields("LanguageID")
       .MoveNext
Wend

will print the contents of the table while not .EOF(End of File)

lets say that I have a text box on the form called txtPatientID and txtLanguageID

how can I add the value that is in these field to the table??

Newbie in search of knowledge

 

[JerryKlmns]

Using a recordset

With rs
   .ActiveConnection = cn
   .CursorLocation = adUseServer
   .CursorType = adForwardOnly
   .LockType = adLockOptimistic
   .Source = "SELECT PatientID, Language FROM tblPatientLanguage"
   .Open
   .AddNew 
   .Fields("PatientID")= txtPatientID 
   .Fields(1)= txtLanguageID
   .Update
   .Close
End With

Or the connection object

cn.Execute "Insert Into tblPatientLanguage (PatientID, Language ) " & 
           "Values (" & txtPatientID & ", " & txtLanguageID  &")",,129

 

[vttech]

In my attempt to organize the code in a more moduler state I
Created a module called ConnectCloseDB that contains two funtions

OpenDB() and Close DB

Function OpenDB()
Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset

Set cn = New ADODB.Connection
Set rs = New ADODB.Recordset

With cn
  .Provider = "Microsoft.Jet.OLEDB.4.0"
  .Properties("Data Source") = "c:\Documents and Settings\cspence\Desktop\CHCB_Projects\NewAmericans.mdb"
'  .Properties("Jet OLEDB:System database") = "\\serv1\db\NCAA.mdw"
'  .Properties("User ID") = TheUserID
'  .Properties("Password") = ThePassword
'  .Properties("Persist Security Info") = False
  .Properties("Mode") = adModeShareDenyNone
  .Open
End With


With rs
   .ActiveConnection = cn
   .CursorLocation = adUseServer
   .CursorType = adOpenStatic
   .LockType = adLockReadOnly
   .Source = "SELECT * FROM tblPatientLanguage"
   .Open
End With

End Function


Function CloseDB()

rs.Close

End Function

And in the main form I left

Private Sub cmdSumbit_Click()

OpenDB

While Not .EOF
       MsgBox "PatientID :" & .Fields("PatientID") & " and Language :" & .Fields("LanguageID")
       .MoveNext
   Wend

CloseDB
    
End Sub

I get the following error “ Complile Error: Invalid or unqualfied reference”

The debug leads me to .EOF

I thought by opening the database and accomplishing the recordset with OpenDB function that I would be able to run task to be able to add, edit and use msgbox.


What am I missing???

Newbie in search of knowledge

 

[vttech]

I figured out I have to make the function Global. But I have never done that before. How do you make a function Global?

Newbie in search of knowledge

 

[JoeAtWork]

Just put it in any code module, and instead of declaring it "Private" declare it "Public".
E.G.

Public Sub FavouriteMessage()
    MsgBox "Hello World!"
End Sub

 

[JerryKlmns]

A little more help to clear things

Module Level
------------------------------
Option Explicit
Public cn As ADODB.Connection
------------------------------

Public Sub OpenDB()
On Error GoTo Err_OpenDB


Set cn = New ADODB.Connection
With cn
  .Provider = "Microsoft.Jet.OLEDB.4.0"
  .Properties("Data Source") = "c:\Documents and Settings\cspence\Desktop\CHCB_Projects\NewAmericans.mdb"
'  .Properties("Jet OLEDB:System database") = "\\serv1\db\NCAA.mdw"
'  .Properties("User ID") = TheUserID
'  .Properties("Password") = ThePassword
'  .Properties("Persist Security Info") = False
  .Properties("Mode") = adModeShareDenyNone
  .Open
End With

Exit_OpenDB:
   Exit Sub

Err_OpenDB:
Dim OpenErr As ADODB.Error
Dim strErrors As String

For Each OpenErr In cn.Errors
   strErrors = strErrors & "Error No:" & OpenErr.ErrorNumber & _
               "Description :" & OpenErr.Description & _
               "Source :" & OpenErr.Source & _
               "NativeError :" & OpenErr.NativeError & _
               "SQLState :" & OpenErr.SQLState & vbNewLine
Next
MsgBox strErrors, vbCritical, "Errors Opening DB"
Set cn = Nothing
Resume Exit_OpenDB

End Sub

Sub CloseDB()

If cn.State = adStateOpen Then cn.Close
Set cn = Nothing

End Sub

Private Sub cmdSumbit_Click()

Dim rs As ADODB.Recordset

Call OpenDB

If cn.State = adStateOpen Then

   Set rs = New ADODB.Recordset
   With rs
      .ActiveConnection = cn
      .CursorLocation = adUseServer
      .CursorType = adOpenStatic
      .LockType = adLockReadOnly
      .Source = "SELECT * FROM tblPatientLanguage"
      .Open
       While Not .EOF
          MsgBox "PatientID :" & .Fields("PatientID") & " and Language :" & .Fields("LanguageID")
       .MoveNext
       Wend
      .Close
   End With
   Set rs = Nothing
End If

Be aware that if you need to update or add new records use appropriate values for CursroType, CursorLocation and LockType.