Solved

There is aproblem in establishing connection to DB

Posted on 2004-04-09
25
360 Views
Last Modified: 2012-05-04
I’m writing a macro in VB editor of Microsoft Outlook 2002.

The purpose of the macro is to fire after every mail arrives and checks for certain criteria in the subject field: if it matches with the criteria, then it reads the body of the mail and stores the certain contents in to variables.

Till now I could able to do this.

Now I have to update a table in MSACCESS 97 with these variables.

In continuation of the above macro when I tried to establish a connection to the Access like
Dim adoConn As New ADODB.Connection
 Dim adoRS As New ADODB.Recordset
 Dim strConn As String
    strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source="
    strConn = strConn & "c:\ Mail.mdb"
    adoConn.ConnectionString = strConn
    adoConn.Open

It is giving me a compile error

Use defined type not defined

Can any body help me please?

Thank You
0
Comment
Question by:LakshmanaRavula
  • 13
  • 7
  • 3
  • +2
25 Comments
 
LVL 49

Expert Comment

by:Ryan Chong
ID: 10791830
Make sure you add the M$ ActiveX Data Object Library from References
0
 
LVL 11

Expert Comment

by:jmwheeler
ID: 10791853
In the VB editor, goto Tools -> References and add a check mark in the box next to Microsoft ActiveX Data Object Library 2.5  (could be 2.0, or 2.1 depending on your computer).
0
 

Author Comment

by:LakshmanaRavula
ID: 10791923
yes I made it
but still getting the same error
and highlighting

Dim tbl As TableDef
0
 
LVL 49

Expert Comment

by:Ryan Chong
ID: 10791980
Using DAO ?? (Weird, why use both DAO and ADO in a same application?)

Try add the M$ DAO Object Library from References, then define the tbl as below:

Dim tbl As DAO.TableDef
0
 

Author Comment

by:LakshmanaRavula
ID: 10792014
Now I ve made my code to look like this  It is giving me no error but not updating the table can you please fix the error

Dim adoConn As New ADODB.Connection
 Dim adoRS As New ADODB.Recordset
 Dim strConn As String
    strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source="
    strConn = strConn & "c:\LAKSHMANA\Mail.mdb"
    adoConn.ConnectionString = strConn
    adoConn.Open
   
Dim tbl As Recordset


    Set tbl = adoConn.OpenRecordset("Mail")
        With tbl
         .AddNew
        ![Start Date] = StartTime
        ![End Date] = EndTime
        ![PNMP Ticket] = Ticket
        ![Reason for Maintenance] = Reason
        ![Locations] = Location
        End With
       
    adoRS.Close
    Set adoRS = Nothing
    adoConn.Close
    Set adoConn = Nothing
0
 

Author Comment

by:LakshmanaRavula
ID: 10792296
When I modified the string to open the table, like this

adoRS.Open ("Mail")

it is giving me an run time error 3709

The connection cannot be used to perform this operation. It is either closed or invalid in this context
0
 
LVL 18

Expert Comment

by:Sethi
ID: 10792297
Declare this as:
Dim tbl As ADODB.Recordset

This is because when you have both DAO and ADO on a machine VB at times gets confused about the Recordset being decalred as both DAO and ADO have recordset objects.
0
 
LVL 18

Expert Comment

by:Sethi
ID: 10792349
Your code till this point is correct:
Dim adoConn As New ADODB.Connection
 Dim adoRS As New ADODB.Recordset
 Dim strConn As String
    strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source="
    strConn = strConn & "c:\LAKSHMANA\Mail.mdb"
    adoConn.ConnectionString = strConn
    adoConn.Open

Now use the following to open a recordset for table named mail:
adoRS.Open "Select * from Mail", adoConn, adOpenForwardOnly, adLockReadOnly, 1

This will only get the data from the table.

For insert, update and delete you should not use recordsets as it takes away lot of system resources, locks the database for longer periods and is slower for these execution.

use the Execute method of connection object directly.

In case you still want to persist with recordset addition the way you are doing, then open the recordset as dynamic and locks as optimistic like this:

adoRS.Open "Select * from Mail", adoConn, adOpenDynamic, adLockOptimistic

0
 

Author Comment

by:LakshmanaRavula
ID: 10792355
Ok that is taken care as you have seen in my code.

But there is a problem in opening the table
0
 

Author Comment

by:LakshmanaRavula
ID: 10792392
As I explained earlier I have to add the values in the table which are already stored in variables
Now I'm using

adoRS.Open "Select * from Mail", adoConn, adOpenDynamic, adLockOptimistic

But how to add the data

adoRS.Open "Select * from Mail", adoConn, adOpenDynamic, adLockOptimistic

        With adoRS
         .AddNew
        ![Start Date] = StartTime
        ![End Date] = EndTime
        ![PNMP Ticket] = Ticket
        ![Reason for Maintenance] = Reason
        ![Locations] = Location
        End With
0
 

Author Comment

by:LakshmanaRavula
ID: 10792406
When I 'm using the above I'm getting the error 3219
Operation is not allowed in this context
0
 

Author Comment

by:LakshmanaRavula
ID: 10792421
Sethi,
As i'm in very begining level in VB can you please explain  

use the Execute method of connection object directly.

what you have Quoted and how to adopt  to my case
0
What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
LVL 18

Expert Comment

by:Sethi
ID: 10792436
I never insert data this way so i wont be in a good position to guide you through recordset. I always use execute method
0
 

Author Comment

by:LakshmanaRavula
ID: 10792501
What is execute method?
0
 
LVL 18

Expert Comment

by:Sethi
ID: 10792551
OK...the first thing that you use in ADO is open a Connection object. To work with the data in your database you can either use a recordset or a connection object directly. Actually in ADO there are more than one ways to achieve things.

The connection object has an Execute method. You have to pass a SQL query to this execute method and the connection object will execute this query in the database. In your case it will be like this:

Dim strSQL as String

strSQL = "Insert into Mail Values(#" & StartTime & "#, #" & EndTime & "#, '" & Ticket & "', '" & Reason & "', '" & Location & "')"
objConn.Execute strSQL
0
 
LVL 18

Expert Comment

by:Sethi
ID: 10792563
In the above query I have assumed that Start Time and End Time are of date/time type. If not then use the following query:
strSQL = "Insert into Mail Values('" & StartTime & "', '" & EndTime & "', '" & Ticket & "', '" & Reason & "', '" & Location & "')"
0
 

Author Comment

by:LakshmanaRavula
ID: 10792661
Thank You sethi,

But now it is neither giving error message nor updating the table.

How to findout the table is opened or not ?
0
 
LVL 18

Expert Comment

by:Sethi
ID: 10792682
The code mentioned above will surely insert data in the table. Have you opened the Access database and seen the Mail table?
0
 

Author Comment

by:LakshmanaRavula
ID: 10792704
Yes Sir,
i 've opened it and checked it and executed several times But the data is not been loaded in to the table
0
 

Author Comment

by:LakshmanaRavula
ID: 10792733
As I'm using the VB editor from MS Outlook, does it makes any difference ?
0
 
LVL 18

Expert Comment

by:Sethi
ID: 10792834
VB Editor from Outlook? I have never used it before. So I can't comment on that.
0
 

Author Comment

by:LakshmanaRavula
ID: 10792841
Any body else can help me please
0
 
LVL 27

Accepted Solution

by:
Dabas earned 500 total points
ID: 10793938
Hi LakshmanaRavula:
>         With adoRS
>          .AddNew
>         ![Start Date] = StartTime
>         ![End Date] = EndTime
>         ![PNMP Ticket] = Ticket
>         ![Reason for Maintenance] = Reason
>         ![Locations] = Location
>         End With
You have not used .Update anywhere!!!



Dabas
0
 
LVL 49

Expert Comment

by:Ryan Chong
ID: 10796769
Hi LakshmanaRavula,

Try use what Sethi suggested above on using the Insert Statement instead? It will be always better to use a conn.execute method, rather than using a .AddNew method. The insert Statement SHOULD work and insert your data into the database, in the case the Syntax of the Statement is correct.

How about output your statement, then paste it into the Access Query Builder to run it, see whether the record is inserted or not.

As already stated:

strSQL = "Insert into Mail Values('" & StartTime & "', '" & EndTime & "', '" & Ticket & "', '" & Reason & "', '" & Location & "')"

then add:

debug.print strSQL

then go the immediate window to copy the statement, then paste and run it at Access Query Builder.

So, is that working?
0
 

Author Comment

by:LakshmanaRavula
ID: 10805592
Thank You Dabas it is working
actually I missed the update factor
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

There are many ways to remove duplicate entries in an SQL or Access database. Most make you temporarily insert an ID field, make a temp table and copy data back and forth, and/or are slow. Here is an easy way in VB6 using ADO to remove duplicate row…
Introduction While answering a recent question about filtering a custom class collection, I realized that this could be accomplished with very little code by using the ScriptControl (SC) library.  This article will introduce you to the SC library a…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

758 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now