Pass data to variables from Access database

[Adlorem]

I have found several examples how to pass data to Access Database, but need small example how to get data from Access and use it as variable using Select query. Any help ?

 

[Adlorem]

Here is my code fo far

Const DBNAME = "H:\Attachmate\CustDat.mdb"

Dim cs As String, cn As Object, cmd As Object, rs As Object, rec As Object

cs = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & DBNAME & ";Persist Security Info=False"    

Set cn = CreateObject("ADODB.Connection")
Set cmd = CreateObject("ADODB.Command")

cn.ConnectionString = cs
cn.Open
cmd.ActiveConnection = cn

strSQL1 = "Select tblCustomers.PrimaryName  from tblCustomers WHERE tblCustomers.ID=10"

cmd.CommandText = strSQL1

Set rec = cmd.Execute

Set cmd = Nothing
Set cn = Nothing

id$ = rec

 

[SkipVought]

Hi,

Make your procedure a FUNCTION that returns a value. In this instance, pass the Customer ID as a parameter...

Function WhatCustomer(CID) As String
    Dim sSQL As String
    Dim rst As ADODB.Recordset, cnn As ADODB.Connection, cmd As ADODB.Command
    Const DBNAME = "H:\Attachmate\CustDat.mdb"
    
    Set cnn = New ADODB.Connection
    Set cmd = New ADODB.Command
    Set rst = New ADODB.Recordset
    
    cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & DBNAME & ";Persist Security Info=False"
    
    sSQL = "Select tblCustomers.PrimaryName  from tblCustomers WHERE tblCustomers.ID=?"
    
    With cmd
         .CommandText = sSQL
         .CommandType = adCmdText
         .Prepared = True
         .Parameters.Append .CreateParameter( _
             "ID", _
             adChar, _
             adParamInput, _
             7, _
             CID)
         
         .ActiveConnection = cnn
         
        Set rst = .Execute
    End With
    
    On Error Resume Next
    
    rst.MoveFirst
    
    If Err.Number = 0 Then
        WhatCustomer = rst(0)
    Else
        WhatCustomer = "NONE"
    End If
    
    rst.Close
    cnn.Close
    
    Set cmd = Nothing
    Set rst = Nothing
    Set cnn = Nothing
End Function

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[Adlorem]

Thank you for your time. It took me a moment to understand your code. Meanwhile I switched to SQL database and came up with this idea that works

Dim cs As String, cn As Object, cmd As Object, rs As Object, rc As Object
Dim ListClients() as String


cs =  "driver={SQL Server};database=Customers;server=PLSQL02;uid=sa;pwd=; Trusted_Connection=yes"

Set cn = CreateObject("ADODB.Connection")
Set cmd = CreateObject("ADODB.Command")
Set rc = CreateObject("ADODB.Recordset")

cn.ConnectionString = cs
cn.Open
cmd.ActiveConnection = cn


strSQL10 = "SELECT count (*) As RecordNumber FROM tblCustomers Where SecondaryName = '"+CusDialog.A06$+"'"

rc.open strSQL10, cn

NumberRecords = rc.fields("RecordNumber").value