How to insert data in a sql 2005 table with Access 2010 front end-using VBA

Have u tried using ADO?

You can try using a connection string or your ODBC connection

Dim adoConn As ADODB.Connection
    Dim objCom As ADODB.Command
    Dim objRS As ADODB.Recordset

    
    Set adoConn = New ADODB.Connection


'CONNECT ONE OF TWO WAYS
    'look here for examples http://www.connectionstrings.com/
    adoConn.ConnectionString = "connection string goes here "
    adoConn.Open
'OR
    adoConn.Open "DSN Name", "UID", "PASSWORD"

    
    
'DEFINE SQL
    Set objCom = New ADODB.Command
    
    With objCom
        .CommandText = "insert into UnitTable (UnitName, UnitDescription) values ('aa','bb')"
        .ActiveConnection = adoConn
    End With
    Set objRS = objCom.Execute

'CLOSE
    objRS.Close
    adoConn.Close
    Set objCom = Nothing
    Set adoConn = Nothing

Select allOpen in new window

I have this error message "Run-time error '3709':
Requested operation requires an OLE DB session object, which is not supported by the current provider" I think there are issues with connection.  

I activated this command

adoConn.Open "LIMS", "sa", "apple"

Should  I do something different?  I would prefer the 1st option of connection but I need help with connection string.  I visited the site but not sure how to get the string.

My sql server name is DPAWAR-HP\SQLEXPRESS
The root directory is c:\Program Files (x86)\Microsoft SQL Server\MSSQL.1\MSSQL

Thanks.

Hi

I'm a bit confused by the line in the question:
>> "connected to Access 2010 by ODBC connection and Access is access project file"

By Access Project - do you mean an ADP?  In which case you're definitely not using ODBC.
If that is the case, then the insert is very trivial. (Stealing from Rocki's example...)

CurrentProject.Connection.Execute "INSERT INTO UnitTable (UnitName, UnitDescription) VALUES ('aa','bb')"

If not an ADP, what you mean by "connected to Access 2010 by ODBC"?
Linked tables?  In which case - you're essentially verbatim. (i.e. you could use the exact same code - allowing for linked table name etc.)
If the linked table isn't updatable - then that's different.  Your server table really needs to have a primary key index and also the Jet linked table needs to have identified that (you can specify it when you're linking - in the Access UI or in code.)

Cheers.

Here is what I did: 1. Created Database Lab SQL server 2005 using SQL server management studio (Screen shot attached)
2. Created ODBC connection linking LIMS database  (Screen shot attached)
3. Created Microsoft Access project file  (Screen shot attached)
4. When asked for connection linked it  (Screen shot attached)

I hope this simplifies it.

Question-13.docx

Yeah you're using an ADP.
The second step you performed (creating an ODBC DSN) wasn't necessary.  
The ADP connection properties are all it needs (as it can't use a DSN as it connects to the server via OLEDB not ODBC).

All you need it the code suggested.

Cheers.

The code is stopping at this point

Please see attachment
Question-13-2.docx

Noo... I mean the code I suggested.
Literally nothing else other than:

CurrentProject.Connection.Execute "INSERT INTO UnitTable (UnitName, UnitDescription) VALUES ('aa','bb')"

Select allOpen in new window

Do I need the Connection strings?

No.  Have you just tried it yet?  Literally the code I suggested in my last post.  Nothing else.
(Obviously adjust the SQL statement as required for what you're wanting to insert.)

The error says "Run time error '3709': Requested operation requires an OLE DB Session object, which is not supported by the current provider.

Here is the code I am of yours;  I am not a professional programmer so please tell code to use if I am a mistake.

Public Sub Insert()
Dim adoConn As ADODB.Connection
    Dim objCom As ADODB.Command
    Dim objRS As ADODB.Recordset

   
    Set adoConn = New ADODB.Connection


'CONNECT ONE OF TWO WAYS
    'look here for examples http://www.connectionstrings.com/
    'adoConn.ConnectionString = "connection string goes here "
    'adoConn.Open
'OR
    'adoConn.Open "DSN Name", "UID", "PASSWORD"

   
   
'DEFINE SQL
    Set objCom = New ADODB.Command
   
    With objCom
        .CommandText = "insert into UnitTable (UnitName, UnitDescription) values ('aa','bb')"
        .ActiveConnection = adoConn
    End With
    Set objRS = objCom.Execute

'CLOSE
    objRS.Close
    adoConn.Close
    Set objCom = Nothing
    Set adoConn = Nothing
End Sub

And when I click debug

The code

.ActiveConnection = adoConn

turns yellow background

Perfect.  Thanks for your help.  I will be posting more questions so is there a way you could help me there too.  

You never know.  If you relate the question to this one (you can do that when you ask it) then particpants here should get a notification.

Cheers.