Solved

AN ADO ERROR IN VISUALBASIC

Posted on 2002-04-19
18
274 Views
Last Modified: 2013-11-25
I got a connection string with ADO. It works fine on my
machin and when i made an executable of my programme and
run the executable on other machine it gives me typemismatch error. I put messaboxes everwhere in my
connection and found it is doing at an initialization of
recordset. Below is the code i m using to connect.
---->

Dim Conn As ADODB.Connection
Dim RS As ADOR.Recordset
Dim AdoCmd As ADODB.Command
Dim ConnString As String
Dim ORCLServerConnect As String

Set Conn = CreateObject("ADODB.Connection")
Set RS = CreateObject("ADOR.Recordset")
ORCLServerConnect = "driver={Microsoft ODBC for Oracle};" & _
                                 "server=" & Me.ServerName & ";" & _
                                 "PWD=" & g_sPassword & ";" & _
                                 "UID=" & g_sUserName & ";"

RS.ActiveConnection = ORCLServerConnect

'----->

It gives me error at:
Set RS = CreateObject("ADOR.Recordset")

It works fine on my machine, even the executable runs fine on my machine. It gives typemismacth on a different machine.

I checked the dll's, registration everything is same and perfect.

VM
0
Comment
Question by:vmandem
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 8
  • 4
  • 3
  • +3
18 Comments
 
LVL 5

Expert Comment

by:rkot2000
ID: 6954880
you may have a differn ado.
try to run mdac_ setup an clients pc.
0
 
LVL 16

Expert Comment

by:Richie_Simonetti
ID: 6954973
I am with rkot.
Besides, try to change:
Set RS = CreateObject("ADOR.Recordset")
to
Set RS = new ADOR.Recordset
(same to others CreateObject)
since CreateObject is used if you don't use strong typed variables (dim RS As Object).
0
 
LVL 16

Expert Comment

by:Richie_Simonetti
ID: 6954981
You can pass the ActiveConnection property when you open RS
RS.open "sqlstatement",conn
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 16

Expert Comment

by:Richie_Simonetti
ID: 6954995
something like:

Private Sub Form_Load()
Dim Conn As ADODB.Connection
Dim RS As ADOR.Recordset
Dim AdoCmd As ADODB.Command
Dim ConnString As String

ConnString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\FMC\Register\centros.mdb;Persist Security Info=False;Jet OLEDB:Database Password=xxxx"
Set Conn = New ADODB.Connection
Set RS = New ADOR.Recordset
With Conn
    .ConnectionString = ConnString
    .Open
    If .State = adStateOpen Then
        RS.Open "select * from centros order by cod_centro", Conn, adOpenDynamic, adLockOptimistic
    End If
End With
Do While Not RS.EOF
    Debug.Print RS.Fields(0)
    RS.MoveNext
Loop
End Sub
0
 
LVL 75

Accepted Solution

by:
Anthony Perkins earned 20 total points
ID: 6956316
Don't use ADOR use ADODB instead.  ADOR is a subset of ADODB is uses MSADO15.DLL and exists only to maintain backward compatibility.

Also as Richie has pointed out:
1. Use early binding instead of late binding, where possible.
2. Use the connection object.

Change your code as follows:

Dim Conn As ADODB.Connection
Dim RS As ADODB.Recordset
Dim AdoCmd As ADODB.Command

Set Conn = New ADODB.Connection
With Conn
   .ConnectionString = "driver={Microsoft ODBC for Oracle};" & _
                       "server=" & Me.ServerName & ";" & _
                       "PWD=" & g_sPassword & ";" & _
                       "UID=" & g_sUserName & ";"
   .Open
End With

Set RS = New ADODB.Recordset
With RS
   .Source = "Select ..."
   Set .ActiveConnection = Conn
   .CursorType = ???
   .LockType = ???
   .Open Options:= adCmdText

   ...

   .Close
End With
Set rs = Nothing
Conn.Close
Set Conn = Nothing

Anthony
0
 

Expert Comment

by:spiral
ID: 6957349
usually i using ado with oledb provider.

Dim Conn As ADODB.Connection
Dim RS As ADODB.Recordset
Dim AdoCmd As ADODB.Command
Dim ConnString As String

Dim ORCLServerConnect As String

Set Conn = new ADODB.Connection
Set RS = new ADODB.Recordset

conn.open "Provider=OraOLEDB.Oracle;" & _
                    "Data Source=" & ServerName & ";" & _
                    "User Id=myUsername;" & _
                    "Password=myPassword;"


sql5 = "............."

with rs
  .cursorlocation = aduseserver
  .open sql5, conn, adOpenDynamic, adLockOptimistic
end with
 

another url to learn oledb connections using oracle.

http://download-west.oracle.com/otndoc/oracle9i/901_doc/win.901/a90171/using.htm

cheers!
0
 
LVL 16

Expert Comment

by:Richie_Simonetti
ID: 6957460
spiral, waht is the idea to post a comment that is so similar to previous one?
0
 

Author Comment

by:vmandem
ID: 6957725
I will try and let you know guys. I appreciate your comments.

VM
0
 

Author Comment

by:vmandem
ID: 6959846
Richie,acperkins

I get the following error after using the early binding technique:

Login Error: 430 , Class doesn't support Automation at

Set RS = New ADOR.Recordset

I didnot find any usefull help from MSDN or anywhere.
What is the alternative.

As I said it works fine on my machine and compiled successfully and made .exe file, no problem running the
.exe file on my machine but give the above error in a different machine.

So which is appropriate to use like the early binding or latebinding.

VM
0
 
LVL 16

Expert Comment

by:Richie_Simonetti
ID: 6959910
well, use adodb as ac saids and not ador and try.
0
 

Expert Comment

by:VBMeera
ID: 6961921
Give reference of MS object Library 2.0 and Use Adodb instead of Ador
0
 

Author Comment

by:vmandem
ID: 6962693
VBMEERA

Eventhough you are absoultly right, i have to give points
to acperkins, since i got the answer from him. There is
nothing not to accept your answer. I hope you understand
the suituation.

VM
0
 

Author Comment

by:vmandem
ID: 6962701
acperkins

I really appreciate your answer.

I really appreciate richie simonetti's answer.

I really appreciate everybody's comments. I really thank
everyone for there support and hope continue the same

VM
0
 
LVL 16

Expert Comment

by:Richie_Simonetti
ID: 6962706
:O
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 6962853
Richie,

Te debo una. <g>

Anthony
0
 
LVL 16

Expert Comment

by:Richie_Simonetti
ID: 6963207
Bueno....(grrrrrrrrr!!!!!), viste el top 15?
Que jugador!!!  ....:))))))))
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 6963418
Richie,

#14: Congratulations!  Give me about 5 years and I will catch up with you.

Anthony
0
 
LVL 16

Expert Comment

by:Richie_Simonetti
ID: 6963527
if you didn't beat me here, maybe i would be at 13! I am not so good with db stuff (i hate it, really)
0

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

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

A theme is a collection of property settings that allow you to define the look of pages and controls, and then apply the look consistently across pages in an application. Themes can be made up of a set of elements: skins, style sheets, images, and o…
Background What I'm presenting in this article is the result of 2 conditions in my work area: We have a SQL Server production environment but no development or test environment; andWe have an MS Access front end using tables in SQL Server but we a…
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
This is Part 3 in a 3-part series on Experts Exchange to discuss error handling in VBA code written for Excel. Part 1 of this series discussed basic error handling code using VBA. http://www.experts-exchange.com/videos/1478/Excel-Error-Handlin…
Suggested Courses
Course of the Month7 days, 4 hours left to enroll

623 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