Solved

AN ADO ERROR IN VISUALBASIC

Posted on 2002-04-19
18
267 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
  • 8
  • 4
  • 3
  • +3
18 Comments
 
LVL 5

Expert Comment

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

Expert Comment

by:Richie_Simonetti
Comment Utility
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
Comment Utility
You can pass the ActiveConnection property when you open RS
RS.open "sqlstatement",conn
0
 
LVL 16

Expert Comment

by:Richie_Simonetti
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
spiral, waht is the idea to post a comment that is so similar to previous one?
0
 

Author Comment

by:vmandem
Comment Utility
I will try and let you know guys. I appreciate your comments.

VM
0
 

Author Comment

by:vmandem
Comment Utility
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
Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

 
LVL 16

Expert Comment

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

Expert Comment

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

Author Comment

by:vmandem
Comment Utility
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
Comment Utility
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
Comment Utility
:O
0
 
LVL 75

Expert Comment

by:Anthony Perkins
Comment Utility
Richie,

Te debo una. <g>

Anthony
0
 
LVL 16

Expert Comment

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

Expert Comment

by:Anthony Perkins
Comment Utility
Richie,

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

Anthony
0
 
LVL 16

Expert Comment

by:Richie_Simonetti
Comment Utility
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

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Suggested Solutions

Introduction While answering a recent question (http://www.experts-exchange.com/Q_27402310.html) in the VB classic zone, I wrote some VB code in the (Office) VBA environment, rather than fire up my older PC.  I didn't post completely correct code o…
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
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…

744 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

16 Experts available now in Live!

Get 1:1 Help Now