[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

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

Posted on 2011-05-12
14
Medium Priority
?
574 Views
Last Modified: 2012-05-11
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?
0
Comment
Question by:dpawar
  • 7
  • 6
14 Comments
 
LVL 65

Expert Comment

by:rockiroads
ID: 35748390
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
 

Author Comment

by:dpawar
ID: 35748698
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
 
LVL 44

Expert Comment

by:Leigh Purvis
ID: 35748904
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
Transaction-level recovery for Oracle database

Veeam Explore for Oracle delivers low RTOs and RPOs with agentless transaction log backup and transaction-level recovery of Oracle databases. You can restore the database to a precise point in time, even to a specific transaction.

 

Author Comment

by:dpawar
ID: 35749019
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
 
LVL 44

Expert Comment

by:Leigh Purvis
ID: 35749052
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
 

Author Comment

by:dpawar
ID: 35749281
The code is stopping at this point

Please see attachment
Question-13-2.docx
0
 
LVL 44

Expert Comment

by:Leigh Purvis
ID: 35749500
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
 

Author Comment

by:dpawar
ID: 35755059
Do I need the Connection strings?
0
 
LVL 44

Expert Comment

by:Leigh Purvis
ID: 35755084
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
 

Author Comment

by:dpawar
ID: 35755373
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
 

Author Comment

by:dpawar
ID: 35755386
And when I click debug

The code

.ActiveConnection = adoConn

turns yellow background
0
 
LVL 44

Accepted Solution

by:
Leigh Purvis earned 2000 total points
ID: 35755447
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
 

Author Comment

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

0
 
LVL 44

Expert Comment

by:Leigh Purvis
ID: 35755609
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

Featured Post

Veeam and MySQL: How to Perform Backup & Recovery

MySQL and the MariaDB variant are among the most used databases in Linux environments, and many critical applications support their data on them. Watch this recorded webinar to find out how Veeam Backup & Replication allows you to get consistent backups of MySQL databases.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
Windows Explorer lets you open cabinet (cab) files like any other folder. In VBA you can easily handle normal files and folders, but opening and indeed creating cabinet files takes a lot more - and that's you'll find here.
Viewers will learn how the fundamental information of how to create a table.
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
Suggested Courses

834 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question