• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 377
  • Last Modified:

There is aproblem in establishing connection to DB

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
LakshmanaRavula
Asked:
LakshmanaRavula
  • 13
  • 7
  • 3
  • +2
1 Solution
 
Ryan ChongCommented:
Make sure you add the M$ ActiveX Data Object Library from References
0
 
jmwheelerCommented:
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
 
LakshmanaRavulaAuthor Commented:
yes I made it
but still getting the same error
and highlighting

Dim tbl As TableDef
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
Ryan ChongCommented:
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
 
LakshmanaRavulaAuthor Commented:
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
 
LakshmanaRavulaAuthor Commented:
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
 
SethiCommented:
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
 
SethiCommented:
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
 
LakshmanaRavulaAuthor Commented:
Ok that is taken care as you have seen in my code.

But there is a problem in opening the table
0
 
LakshmanaRavulaAuthor Commented:
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
 
LakshmanaRavulaAuthor Commented:
When I 'm using the above I'm getting the error 3219
Operation is not allowed in this context
0
 
LakshmanaRavulaAuthor Commented:
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
 
SethiCommented:
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
 
LakshmanaRavulaAuthor Commented:
What is execute method?
0
 
SethiCommented:
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
 
SethiCommented:
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
 
LakshmanaRavulaAuthor Commented:
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
 
SethiCommented:
The code mentioned above will surely insert data in the table. Have you opened the Access database and seen the Mail table?
0
 
LakshmanaRavulaAuthor Commented:
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
 
LakshmanaRavulaAuthor Commented:
As I'm using the VB editor from MS Outlook, does it makes any difference ?
0
 
SethiCommented:
VB Editor from Outlook? I have never used it before. So I can't comment on that.
0
 
LakshmanaRavulaAuthor Commented:
Any body else can help me please
0
 
DabasCommented:
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
 
Ryan ChongCommented:
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
 
LakshmanaRavulaAuthor Commented:
Thank You Dabas it is working
actually I missed the update factor
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 13
  • 7
  • 3
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now