Saving data to table using unbound form in MS Access 2010 (1 Viewer)

Fahad987

New member
Local time
Today, 13:37
Joined
Dec 15, 2021
Messages
11
Hi,

i have a database with extension .accdb. In the VBA code for Save button, following code is written.

Code:
' Create Variables

Dim cnn1 As ADODB.Connection
Dim rstcontact As ADODB.Recordset
Dim strCnn As String

' Open a connection.
    Set cnn1 = ADODB.Connection
   mydb = "D:\filepath\db1.accdb"
strCnn = "Provider=Microsoft.ACE.OLEDB.14.0;Data Source=" & mydb
    cnn1.Open strCnn

' Open table.
    Set rstcontact = New ADODB.Recordset
    rstcontact.CursorType = adOpenKeyset
    rstcontact.LockType = adLockOptimistic
    rstcontact.Open "tbl001", cnn1, , , adCmdTable

However, it keeps giving error at 'ADODB.Connection' as 'Method or data member not found' while highlighting the .connection word. I have checked the References section. I believe all relevant references are selected there (see the attached picture). So why is it stuck there? What should be done or changed so as to make the code working? Your help is really appreciated.
 

Attachments

  • References Window.png
    References Window.png
    32.5 KB · Views: 218

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 16:37
Joined
May 7, 2009
Messages
19,169
I have Office 2021 and the provider is still the same:

Provider=Microsoft.ACE.OLEDB.12.0
 

Fahad987

New member
Local time
Today, 13:37
Joined
Dec 15, 2021
Messages
11
so anything wrong with the code ?? or should i use DAO ?
 

Fahad987

New member
Local time
Today, 13:37
Joined
Dec 15, 2021
Messages
11
Changed to 12, but still getting the same compile error message.
 

LarryE

Active member
Local time
Today, 01:37
Joined
Aug 18, 2021
Messages
562
I don't believe you need:
Set cnn1 = ADODB.Connection
because you have already defined it as a connection
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 16:37
Joined
May 7, 2009
Messages
19,169
don't you think you need to create New connection first.

Set cnn1 = New ADODB.Connection
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 04:37
Joined
Feb 19, 2002
Messages
42,981
Access is a Rapid Application Development (RAD) tool. If you are not using bound forms/reports, you are not using the ONLY feature that makes Access worthwhile. You should almost certainly be using a different platform if you don't want to use the feature of Access which make it a great tool. Access comes with a lot of baggage and a very heavy footprint. You are accepting the worst parts and eschewing the best.
 

piano_playr

Member
Local time
Today, 01:37
Joined
Oct 31, 2014
Messages
30
Access is meant to be used with linked tables and bound forms and reports. That is the advantage of the platform and should be used in that manner to enjoy its benefits. However, some like to adapt Access to using dynamically generated data for unbound form controls. I have played around with this technique for fun to see how far I can take it. I typically use SQL Server on the backend. That requires a connection string to the SQL Server. This is a small detail because the ADO code is still the same. I created an ADO recordset to connect to my SQL Server database.

I'm currently using SQL Server 2014. The sqloledb driver works fine to connect to the SQL Server database. This technique for opening a recordset from anywhere is useful because the ADO variable names are global . A form with unbound controls can be populated with a combo box. See the second code block below. As you can see, it is a customer form. The data is pulled from SQL Server. Please note, that this is all done dynamically, i.e. there are NO linked tables. The recordset property of the combo box is dynamically populated too. After the form is opened, user is presented with a selection combo box and blank textboxes. The combo box sits at the top of the form allowing user to pick a record. Once the selection is made, the unbound textboxes are populated. See form view below. I have found this only works to populate text boxes. I have not figured out a way to populate a combo box.


Code:
Public g_adoConnection As New ADODB.Connection
Public g_adoCommand As New ADODB.Command
Public g_adoRS As New ADODB.Recordse

Public Sub LoadADORecordSet(strSQL, Optional lngTimeout As Long)
 
  On Error GoTo Err_LoadADORecordSet
 
    If g_adoRS.state = adStateOpen Then g_adoRS.Close
    If g_adoConnection.state = adStateOpen Then g_adoConnection.Close
    
    Dim strConnectionString As String
    strConnectionString = SetConnxn
    
    g_adoConnection.ConnectionString = strConnectionString
    g_adoConnection.Open
    
    g_adoRS.CursorLocation = adUseServer
    g_adoRS.CursorType = adOpenStatic
    
    g_adoRS.Open strSQL, g_adoConnection
 
Exit_LoadADORecordSet:
    Exit Sub

Err_LoadADORecordSet:
    MsgBox "SQL statement could not be executed :" & vbCrLf & strSQL _
           & vbCrLf & Err.Description, vbOKOnly, "Avium " & Err.Source
    g_adoConnection.Close
    Resume Exit_LoadADORecordSet

End Sub

Public Function SetConnxn()

    On Error GoTo HandleError

    SetConnxn = "Provider=sqloledb;Data Source=<SQL_server_name>;" & _
             "Initial Catalog=<database_name>;User ID=<user_name>;Password=<user_password>;trusted_connection=no"
            
HandleExit:
    Exit Function
HandleError:
    MsgBox Err.Description
    Resume HandleExit

End Function

Code:
Private Sub cbo_select_AfterUpdate()

    On Error GoTo HandleError
    
    Dim intCustomer_ID As Integer
    intCustomer_ID = str(Nz(Screen.ActiveControl, 0))

    Dim strSQL As String
    strSQL = "select [customer_id]" & _
                      ", [first_name]" & _
                      ", [last_name]" & _
                      ", [phone]" & _
                      ", [email]" & _
                      ", [street]" & _
                      ", [city]" & _
                      ", [state]" & _
                      ", [zip_code] " & _
          "from [sales].[customers] " & _
          "where customer_id = " & intCustomer_ID
          
    LoadADORecordSet (strSQL)
    
  With Me
    .Customer_ID = g_adoRS.Fields("customer_id").Value
    .first_name = g_adoRS.Fields("first_name").Value
    .last_name = g_adoRS.Fields("last_name").Value
    .phone = g_adoRS.Fields("phone").Value
    .email = g_adoRS.Fields("email").Value
    .street = g_adoRS.Fields("street").Value
    .city = g_adoRS.Fields("city").Value
    .state = g_adoRS.Fields("state").Value
    .zip_code = g_adoRS.Fields("zip_code").Value
  End With
 
  Set g_adoRS = Nothing
 
HandleExit:
    Exit Sub
HandleError:
    MsgBox Err.Description
    Resume HandleExit

Public Sub ADOComboboxSetRS(cmb As ComboBox, strSQL As String)
    
    On Error GoTo HandleError
    
    Dim cn As New ADODB.Connection
    Dim rs As New ADODB.Recordset
    Dim lngCount As Long
    Dim strConnectionString As String
    
    strConnectionString = SetConnxn
    cn.ConnectionString = strConnectionString
    cn.Open
    
    rs.CursorLocation = adUseServer
    rs.CursorType = adOpenStatic
    
    rs.Open strSQL, cn

    If Not rs Is Nothing Then
        If Not (rs.EOF And rs.BOF) Then
            Set cmb.Recordset = rs
            ' enforces the combobox to load completely
            lngCount = cmb.ListCount
        End If
    End If
    
    Set rs = Nothing
    Set cn = Nothing

HandleExit:
    Exit Sub
HandleError:
    MsgBox Err.Description
    Resume HandleExit

End Sub

fCustomer.JPG
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 04:37
Joined
Feb 19, 2002
Messages
42,981
And the point of the exercise is? Just because you can do something doesn't mean you should. Educating yourself and learning how do do something new is always good for you. Have fun.
 

piano_playr

Member
Local time
Today, 01:37
Joined
Oct 31, 2014
Messages
30
And the point of the exercise is? Just because you can do something doesn't mean you should. Educating yourself and learning how do do something new is always good for you. Have fun.
Hi Pat,

Thanks for the comment. I understand your position. The point of the post was to respond to the OP even though the date of the post goes back to last December. I wanted to give him some ideas on how to use data objects (either DAO or ADO). I worked on this some more (only for fun) and made some interesting discoveries.

  • First, I linked the tables in my SQL Server demo database called "BikeStore" using a machine DSN on my workstation. See schema below.
  • Next, I generated a form using the Access form wizard and selected the sales.customers table as the record source.
  • Next, I wrote a snippet for the on-load event of the form (see snippet below). As you can see, it programmatically creates a recordset for the form using ADO and SQL Server data (i.e. Me.Recordset = rstCustomers).
  • Next, I deleted the link to the sales.customers.
  • Next, I loaded the form and found it still works. The MS Access "form navigation control" still works even though the table is not linked in the Access file. Loading the table to set the recordset programmatically allows this to happen. I believe this exercise has some value if the developer does not want to expose his linked tables to user. (That sounds like a good idea to me).
I bet there are some forum members who would like to know about this even though it runs counter to the basic principles of MS Access.

:cool:


Code:
Option Compare Database
Option Explicit

Function fCnn() As String
'Purpose    : connection string
'Author     :
'Description:
'Date       : 17Jun22

    On Error GoTo Error_Handler

    fCnn = "Provider=sqloledb;Data Source=<SQL Server name>;" & _
    "Initial Catalog=<database name>;User ID=<user name>; password=<password>;trusted_connection=no"
   
Exit_Point:
    Exit Function
   
Error_Handler:
   
        MsgBox _
        "An unexpected error has occurred.  The system's description " & _
        "of this error is:" & vbCr & vbCr & _
        "Error " & Err.Number & ": " & Err.Description, _
        vbExclamation, _
        "Unexpected Error"
   
End Function
Sub Form_Load()
'Purpose    : load customer table into form
'Author     :
'Description:
'Date       : 17Jun22

    On Error GoTo Error_Handler
   
    'procedure goes here
    Dim rstCustomers As ADODB.Recordset
    Dim Cnxn As ADODB.Connection
    Dim strCnxn As String
    Dim strSQLCustomers As String
   
    Set Cnxn = New ADODB.Connection
    Set rstCustomers = New ADODB.Recordset
   
    strCnxn = fCnn
    With Cnxn
        .ConnectionString = strCnxn
        .Open
    End With
   
    strSQLCustomers = "SELECT customer_id , first_name , last_name , phone , email , street , city , state , zip_code"
    strSQLCustomers = strSQLCustomers & " FROM sales.customers"
   
    With rstCustomers
        .CursorLocation = adUseServer
        .CursorType = adOpenStatic
        .Open strSQLCustomers, Cnxn
    End With

    Set Me.Recordset = rstCustomers
   
    ' clean up
    rstCustomers.Close
    Cnxn.Close
    Set rstCustomers = Nothing
    Set Cnxn = Nothing
   
Exit_Point:
    Exit Sub
   
Error_Handler:
   
        MsgBox _
        "An unexpected error has occurred.  The system's description " & _
        "of this error is:" & vbCr & vbCr & _
        "Error " & Err.Number & ": " & Err.Description, _
        vbExclamation, _
        "Unexpected Error"
   
End Sub
 

Attachments

  • BikeStores.JPG
    BikeStores.JPG
    72.1 KB · Views: 112
  • sales_customer.JPG
    sales_customer.JPG
    40.3 KB · Views: 105

Pat Hartman

Super Moderator
Staff member
Local time
Today, 04:37
Joined
Feb 19, 2002
Messages
42,981
I bet there are some forum members who would like to know about this even though it runs counter to the basic principles of MS Access.
I'm sure there are. And there may be a real use for this technique under some special circumstance so you may have solved a problem you didn't even know existed.

Access is a Rapid Application Development tool. It is designed to build a certain type of data-centric application. It does many things for you. The power of Access is actually with bound forms and reports. The heavy footprint of Access is there just because of what it does for you. If you don't want to use those features because you think you are smarter than access and want to use unbound forms just because you can, you might want to move to a different environment where you won't have the heavy footprint of Access to get around to do things that Access already does for you.
 

Gasman

Enthusiastic Amateur
Local time
Today, 08:37
Joined
Sep 21, 2011
Messages
14,050
I'm sure there are. And there may be a real use for this technique under some special circumstance so you may have solved a problem you didn't even know existed.

Access is a Rapid Application Development tool. It is designed to build a certain type of data-centric application. It does many things for you. The power of Access is actually with bound forms and reports. The heavy footprint of Access is there just because of what it does for you. If you don't want to use those features because you think you are smarter than access and want to use unbound forms just because you can, you might want to move to a different environment where you won't have the heavy footprint of Access to get around to do things that Access already does for you.
Like Foxpro :)
 

Users who are viewing this thread

Top Bottom