Solved

AN ADO ERROR IN VISUALBASIC

Posted on 2002-04-19
18
268 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
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
 
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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Paint/Redraw window while dragging 16 68
VBA/SQL - Connect to SQL server and pull data 4 89
Please explain "Multi-Tenant Services" 5 65
Help me. 3 48
Article by: Martin
Here are a few simple, working, games that you can use as-is or as the basis for your own games. Tic-Tac-Toe This is one of the simplest of all games.   The game allows for a choice of who goes first and keeps track of the number of wins for…
Entering time in Microsoft Access can be difficult. An input mask often bothers users more than helping them and won't catch all typing errors. This article shows how to create a textbox for 24-hour time input with full validation politely catching …
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…

863 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

21 Experts available now in Live!

Get 1:1 Help Now