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

I have a Unit table in LIMS database in SQL 2005 LIMS is connected to Access 2010 by ODBC connection and Access is access project file. The fields of Unit table are UnitID (Autonumber), UnitName and UnitDescription.  I want to insert UnitName and Unit description using VBA.  How can I do that?
dpawarAsked:
Who is Participating?
 
Leigh PurvisDatabase DeveloperCommented:
OK...
You need to read carefully what I'm saying here.
Execute the following code... And only the following code.

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

Open in new window


That is everything.
0
 
rockiroadsCommented:
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

Open in new window

0
 
dpawarAuthor Commented:
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.
0
Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

 
Leigh PurvisDatabase DeveloperCommented:
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.
0
 
dpawarAuthor Commented:
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
0
 
Leigh PurvisDatabase DeveloperCommented:
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.
0
 
dpawarAuthor Commented:
The code is stopping at this point

Please see attachment
Question-13-2.docx
0
 
Leigh PurvisDatabase DeveloperCommented:
Noo... I mean the code I suggested.
Literally nothing else other than:
CurrentProject.Connection.Execute "INSERT INTO UnitTable (UnitName, UnitDescription) VALUES ('aa','bb')"

Open in new window

0
 
dpawarAuthor Commented:
Do I need the Connection strings?
0
 
Leigh PurvisDatabase DeveloperCommented:
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.)
0
 
dpawarAuthor Commented:
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
0
 
dpawarAuthor Commented:
And when I click debug

The code

.ActiveConnection = adoConn

turns yellow background
0
 
dpawarAuthor Commented:
Perfect.  Thanks for your help.  I will be posting more questions so is there a way you could help me there too.  

0
 
Leigh PurvisDatabase DeveloperCommented:
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.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.