Tracking Forums, Newsgroups, Maling Lists
Home Scripts Tutorials Tracker Forums
  Advanced Search
  HOME    TRACKER    Visual Basic

See Related Forum Messages: Follow the Links Below to View Complete Thread

Can't Find AdOpenKeyset Project Or Library
I'm trying to open an ADO recordset for update by defining my cursor as 'adOpenKeyset'. However, when I try to do that, I get the following error: 'Compile error: Can't find project or library'.

I've gone into my References and checked every library that looked as though it might be remotely related, but still no luck.

Any ideas how I can either: (1) get VBA to recognize adOpenKeyset, or (2) how I can otherwise open the recordset for update?



Hi pals!

May i know how do i use sql together with sql? Meaning..i'm using adodb to connect to my dbase and i need a sql statement to retrieve my data but i do not know the syntax for this..can anyone help? So far, i'm ony aware of sql with DAO...Thanks alot! SmileZ!


I am trying to connect to the back-end (MS Access) from the front end VB 6.0.

I have written a code to connect to the database and check whether
the user exists in the database and then only allow to go ahead further.

Now my problem is that it only compares the text box value with only the first record in the login table of the database and it doesn't search any other value or you can say that it does not compare the other values in the database.

I have written the code as follows:

Dim con As New ADODB.Connection
Dim rs As New ADODB.Recordset

Private Sub cmdsubmit_Click()

con.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:projectprojects.mdb;"

rs.Open "select * from login", con, adOpenDynamic,adLockOptimistic

If (txtusername = rs.Fields(0) And txtpassword.Text = rs.Fields (1)) Then
MsgBox "access granted"
MsgBox "access not granted"
End If

End Sub

Please advise or help me with the correct code.. ASAP

Im attempting to establish a conection with a database and im not sure what reference to include to use ADODB

I am trying to use the command tmprs.MovePrevious where tmprs is a adodb recordset. When I do this I get the following runtime error

Run-Time Error 3219
Operation is not allowed in this context.
Does anybody know what this means or why it is occuring. I don't remember coming across this before.

Hi, I am using adodb in the project. I have a master/detail form. Everytime a record is added in a grid it has to calculate the final received qty which is a result of received qty minus rejection qty. this works fine if the record is already available. but if a new record is appended the code written don't work & gives an error message "could not found row". This can be tested with a simply adding a data wizard form in a project. plz any one could help & find out how I'll overcome this problem!

How do I get an Image box/Picture Box or OLE to work with a ADODB connection??

Hi. It's been many years since I did any DB programming and now I don't really remember how to do anything. What I want to do is:

1. check to see if a database exists (not hard because an error is raised if i try to connect to a database that doesnt exist)
2. if it doesnt exist, create it and add appropriate tables (i cant figure this part out)
3. read and write information from and to the database (not so hard)

hi guys,

total newbie in vb. I'm trying to use ADODB.connection and its other methods (such as ADODB.recordset) to connect to an ODBC database. But it always produces an error saying the user-defined data type is not defined. Is there something that I missed??

pls help.

thx heaps!!

Need Help With ADODB
as you can see in this picture, there are fields:
field 1
field 2
field add
field plus
I want to add a field named "field last", how can I do with ADODB.Connection ?
Also, I want to know for example if a field named "field 5" is available/exists in the corrent table ?! how ?
plz helpppp me.......

First ADODB App...Help Please
I am following a tut on writing all the ADODB stuff myself, instead of using the provided object. I have looked over this code a million times, and I can't quite seem to find the error. VB is telling me there is an error with my insert statement. It appears to be just like the one I seen in the tut, but maybe I am overlooking something. Thanks for the help.

Private Sub cmdProccess_Click()
Dim adoConn As ADODB.Connection
Dim full_number As String
Dim acode As String
Dim num As String

full_number = txtNumber.Text
acode = Left(full_number, 3)
num = Right(full_number, 6)

Set adoConn = New ADODB.Connection
adoConn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:Documents and SettingsTomDesktopDNC Adderdnc.mdb"
adoConn.Execute ("INSERT INTO masterDNC (AreaCode, Number) VALUES('acode', 'num')")
lblStatus.Caption = acode & " " & num & " Added"
End Sub

I am just wonder why in some project, I can: dim cnn as ADODB.Connection,
but some project , I can not.

Is there any place I need to add ADODB into the project?


Hi all,
I am trying to create some variables as data type ADODB.Connection and ADODB.Recordset but when I actually run the program, I seem to get an error message saying Visual Basic 6.0 does not recognize the variable type. Could someone help me with this.
Do I have to go to Project --> References or
Project --> Components
Whichever one it is, could you please tell me? I don't know which reference or component to select?

Is it Microsoft.Jet.OLEDB.3.51 .... something like that?
Anyways, thanx for your help in advance.


What i need to install to use ADO in my VB application??

Thank you, Guilherme Costa

I want to open a SQL conenction using ADODB and I use this:

Dim conn As ADODB.Connection

But It says User-Defined type not defined. Do I need a DLL or something? Help

I have an ADODB Data Control on my form. What is the simplest method of running an insert command against the database (MS Access) that control is hooked up to? Could you give me a code example? Thanks.

Does ADODB connections time out? I have a server on a NT2000Server Machine and a XP Pro client application calling it. Application
is designed to stay open for long periods of time and the connection (unlike the recordset) is only called once at launch
and a con.close at exit. There are no restrictive port properties that we know of on either system that would cause a port to close
without the software command. Any info would be great....


I am creating a visual basic interface to an access 2000 dbase
I can populate various combo boxes (Combo1 to combo6) on a query form from the database quite happily. I have created a query in the dbase which provides all the info I require, But im stuck. I want to generate a report (list boxes) which only shows the records that match the selections of the combo boxes. Each combobox may or maynot have a selection.

A simple 'if and then' with a 'do while loop' using 'recordset and movenext' statement works fine, as long as all comboboxes have a selection, but as soon as a box is left blank, I get no records that macth.

All i am after really is a way to use a wildcard where the combo has no selection

simply using
if combo1.text="" then

Best Way ADODB?
Which of these 4, is best to use to open a connection and retrive data?

1.CODEDim Conn        As ADODB.Connection
Dim Rec         As ADODB.Recordset
Set Conn = CreateObject("ADODB.Connection")

Conn.ConnectionString = c_dbConnectionString
Conn.CursorLocation = adUseServer
Conn.ConnectionTimeout = 10

Set Rec = Conn.Execute(sSQL)


 I m usind an adodb recordset (5.0 lib)
 it is linked to data combo
 some times i get an error 'Unspecified error' -2147467259'
 can ne body tell me hoe to resolve it ?


I have a VB application, the database that i'm using is MS Access 7, the application performs a lot of inserts, deletes and updates.

I want to know what data access technology is better in my case: ADO or DAO???

i have another question, is there a way to avoid that the database grows a lot because of the inserts and updates? (i have to compact it frequently!!!)


ADODB ?? Help Me

I downloaded a code from the Internet to secure the Login to my VB project but am having a problem working with it as shown in the followed photo can u help me plz ??

also attached the sample code i used

Rdo / Adodb
Can I use rdo connection with ADODB recordset. ie; specify rdoConnection as source for opening recordset by ADODB.
ALso how can i set rdoConnection as souce for a datagrid

I'M using an ADODB to UPADTE a database, it looks like

oADODB.Execute("update ...")

That method return an object RecordSet, is this returning the lines updated? Because i must know if i have updated something.

thanks in advance

I am using the following code to delete records from the recordset

If rs.RecordCount > 0 Then
If rs.AbsolutePosition = 1 Then
ElseIf rs.AbsolutePosition = rs.RecordCount Then

End If
MsgBox "There is no record in the table"
End If

Here” rs” is a adodb recordset it’s properties are assigned as follows:
rs.CursorLocation = adUseClient
rs.CursorType = adOpenDynamic
rs.LockType = adLockBatchOptimistic

But there shows an error when there is only one record in the recordset. I don’t want to change locktype property of rs, can anybody help me to solve this problem. Thank you!

Can someone please clarify the difference between these two following sets of declaration

Dim adoconnect as connection
dim adorecordset as recordset


Dim adoconnect as ADODB.Connection
dim adorecordset as ADODB.Recordset

What is the advantage in using ADODB.


How can I open an excel file thru the adodb control.
What would be the provider clause in this case

Like in opening an MS Sql file the connectoion code would be

xlsDB.Provider = "sqloledb"

similarly what would be the code in case of excel and CVS file options thry ADODB controls.

Thanx in advance

Graph And Adodb
hi there,

i want to plot a bar graph using the data from the database in vb.

-database: i'm using adodb connection

i have a database table (transaction) that records the transaction of number of item going in and out in the inventory. Each transaction is recorded with the number of items going out and the date. i think these two information is important in drawing the graph.

i want to plot an annual bar graph ( jan to dec) using the data from the database table (transaction)

Anyone can show me any code samples or websites that could help me in plotting the bar graph.

thanks in advance

Hi, I am using the ADODB code for desigining the database Application. I have used the master/detail form where datagrid is used for the detail table.Relation between master & detail is one to many. I want to calculate the difference of qty received & qty rejected & write into the final qty received in the detail table. if the record is already available this works fine for the event rowcolchange but if a new record is appended I am getting an error message of "row not found" plz. help me is solving this problem.

ADODB Activeconnection
Hi Every one
I am new to VB world

I need some informtion regarding REcordset and connection

what I want to know is about the activeconnection
what is the difference between both

if I open a database like conn

or if I do RS.OPEN(Source,Activeconnections,)

I am looking for something like, I have an application in VBA excel

which runs and make connection with Database

is it possible that once user start the application , it makes the connection with the database and fetches the required information which is needed for userform to initialize and then close the connection
and when user do any activity on the form based on that connection should be made at that point again and after executing the command closed again and so on.

CAn this be done, I have heard about Activeconnection property but want to know more about it.


ADODB Reference?
Hi all,
just a quick question, is there an online ADODB for VBA reference/guide/tutorial anywhere? I dont have the appropriate help files installed where I work.


ADODB And Outlook
Since a couple of days i managed to retrieve data from an excel workbook by using adodb.
Now i wondered, is it also possible with outlook. I'd like to get appointments from the agenda. If it is possible does anyone have a good tutorial?

Thanx in advance,


Adodb, Using Sql To Write
hi guys

i'm using excel 2000 (vba). i'm trying to use ado to copy some data into a closed workbook. i figured out how to set the value manually, "update [m3$a1:a9] set Overall = '1'"

In the example I have a workbook with a tab named "m3", and range("a1").value = Overall. So now, under overall, I have 8 rows with the value 1. great. but I want to be able to use a variable in the update statement if thats possible. for example "update [m3$a1:d9] set overall = myvar" or "update [m3$a1:d9] set overall = 'myvar'"

neither work, i get an "automation error"

here is my code, please ignore the comments, i am a newbie

Option Explicit

Const sourceDB As String = "c:/stat_template.xls" 'path to sourceworkbook
Const targetDB As String = "c:/ado_getStats.xls" ' path to targetworkbook
Public Sub adoRead()

Dim cn As New ADODB.Connection
Dim rs As New ADODB.Recordset
Dim strSQL As String
Dim myVar as Integer
Dim xlwb As Workbook
Dim xlws As Worksheet
Set xlwb = Excel.Workbooks("ado_getStats")
Set xlws = xlwb.Worksheets("s1")
strSQL = "select * from [m3$a1:d9]" 'uses SQL to collect recordset
myVar = 5
With cn 'opens connection
.Provider = "Microsoft.Jet.OLEDB.4.0"
.ConnectionString = "Data Source=" & sourceDB & _
";Extended Properties=Excel 8.0"
End With
rs.Open strSQL, cn 'creates recordset
xlws.Range("b1").CopyFromRecordset rs 'writes recordset to excel
cn.Execute "UPDATE [m3$a1:d9] SET Overall = myVar" ' here I want to write
rs.Close 'closes recordset
cn.Close 'closes connection
Set rs = Nothing
Set cn = Nothing 'clears memory
Set xlws = nothing
Set xlwb = nothing
End Sub

thanks guys your help is appreciated

ADODB Connection
I am attempting to establish a conection with a database currently loaded into a Access.Application object.

heres my code

Dim cat As ADOX.Catalog
Dim conn As ADODB.Connection
Set cat = New ADOX.Catalog

Set conn = accObj.CurrentProject.Connection 'accObj is the loaded
Set cat.ActiveConnection = conn 'access database

I get an error on the last line saying the argurments are the wrong type, out of range, or conflict

please help

Asp Adodb Problem
for some reason i am getting an error "Too few parameters. Expected 1. "

set Conn =Server.CreateObject("ADODB.Connection")
conString = "DBQ=" & Server.MapPath("/_private/hitcounter.mdb")
Conn.Open "DRIVER={Microsoft Access Driver (*.mdb)};" & constring
set hitcount = server.CreateObject("ADODB.Recordset")
sql="Select * from PictureIndex where DisplayGroup = """ & Request.QueryString("grid") & """ order by displayorder"
Response.Write(sql) sql,conn,2,3 --> error is on this line

ADODB ConnectionTimeout
When connecting to DB2 with a query request to return a recordset, a timeout communication error occurs because too much time is taken. I have attempted to optimize my queries, but my question is 'How do I increase my connection timeout range when connecting to DB2?'. I looked at properties and found no connection time, other than the properties of the connection in the data environment, but that value is not permanent.

How can i Link my ADODB control to a DATAGRID??

Error In ADODB
I've gotten the following error:

Multiple-step OLE DB operation generated errors. Check each
OLE DB status value, if available. No work was done.

Question: HOW do check the OLE DB status value? and what
does it mean when I check it?

Adodb.connection Help!!
I am trying to make a function that just tells where something is referenced in another table. The reference goes by id. Here is the code:

Public Function IsRef(table As String, id As String, field As String) As Boolean

Dim sql As String
Dim ado As ADODB.Connection

Set ado = New ADODB.Connection

ado.ConnectionString = connectstr

sql = "select * from " & table & " where " & field & " = '" & id & "'"
ado.Execute (sql)

THe sql is fine. How can I tell if there are any records in ado ?? Because if there is I want isRef = TRUE.

I am reading the Tutorial on ADO in Tute corner...

It gives an example ADO but the code uses a ADODB....

It sugested DLing the Microsoft ActiveX Data Objects 2.x Library and gave a link.....

I downloaded version 2.8..... the latest version... Note; The DL was automated you open it , it finds where its supposed to DL then wa la your done or supposed to be

After restart I should go and find Microsoft ActiveX Data Objects 2.x Library but I dont see it.

What is the dif between ADODB and ADODC?

Anyhelp would be appreciated I am in the dark on this one


ADODB RS To Textboxes
Hi everyone ... With the help of people here and lots of Tutorials, I have gotten part of this project working, but my lack of knowledge and understanding is becoming obvious again.
In this portion, I am taking the User selection to build a SQL that is suppost to return all columns from one table for a specific company.
I think that is working, as debug.print MyRS.recordCount returns 1.
In this portion of the program, the User should be able to change things,
so I need to take each column returned and assign them to unbound textboxes.
The changes would be like the ContactName, Phone# ... etc.
Here is all the revelant code

Private Sub Option1_Click(Index As Integer)

Dim strCoName As String
Dim strSQL As String

strCoName = Option1(Index).Caption
'Some records have 'in the name, that must be removed.

strCoName = Replace(strCoName, "'", "''")

strSQL = "Select * From Suppliers where CompanyName = '" & strCoName & "'" & ";"
modTestConnect.ConnectToDBase strSQL
Text1.Text = MyRS.Fields.Item("ContactName").Value
Text2.Text = MyRS.Fields.Item("ContactTitle").Value

Debug.Print MyRS.Fields.Item("ContactName").Value

'Database connection in a seperate .BAS module
Option Explicit

Public cnMyCon As ADODB.Connection 'Connection to ADODB
Public MyRS As ADODB.Recordset 'Holds records

Public Sub ConnectToDBase(strSQL As String)
Dim lngRecordCount As Long

Set cnMyCon = New ADODB.Connection
Set MyRS = New ADODB.Recordset

MyRS.CursorLocation = adUseClient

cnMyCon.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:Test.mdb;Persist Security Info=False"
cnMyCon.CursorLocation = adUseClient

MyRS.Open strSQL, cnMyCon, adOpenDynamic, adLockOptimistic

If Not MyRS.EOF Or Not MyRS.BOF Then
MyRS.MoveLast 'without these the recordcount
MyRS.MoveFirst 'ends up -1
End If

lngRecordCount = MyRS.RecordCount
Debug.Print "From Mod " & lngRecordCount 'visual proof that it has something
End Sub
The module Debug.print returns 1.
In the Option button click event, when I try to assign to Text1 .. text2, I get a 3021 runtime error ...
"BOF or EOF is true or current record has been deleted. Requested operation requires a current record".
In the module I check for EOF and BOF. And when I step through the code, EOF is False and BOF is False.
So, I am stumped!
Any ideas for me??

I am connecting to a third party database using a C/ODBC driver and MSDASQL as the provider. The connection occurs as neccasary but when I retrieve a recordset, I'm unable to navigate through the object. Even though I know there should be at least 100 records returned I get a Recordcount of -1. I can get the field values for the first record returned but am unable to manuver around to any other record. Is there any work around for a provider that does not support record navigation? Your suggestions are GREATLY appriciated.

Thank you in advance.

ADODB Problem
Dear all,

One of my Collegue is facing a severe problem in the ADODB recordset

He is trying to retereive around 10000 records. He can able to retriev the records into the recordset, But when he is moving to next record, inside the recordset (movenext) he is getting an error "Not enough Storage is available to complete this operation"

Why this is coming and how to solve this problem ? Is it a ADODB problem ?

ADODB Problem
Hi, I've a problem.
I've a ADODB interface to connect with my sql server. Everything works fine until i use parameters above 8kb. Let me try to describe the error.

I've a stored procedure with a declaration like this :

CREATE PROCEDURE dbo.pr_AppAdicionaInteraccaoAoProcesso
@CodProcesso uniqueidentifier,vai ser adicionada a interacção
@CodAccao uniqueidentifier,
@CodCanalTecnologico uniqueidentifier = NULL,
@CodTipoContacto uniqueidentifier = NULL,
@CodTema uniqueidentifier = NULL,
@CodEstado uniqueidentifier = NULL,
@CodTipoEstado uniqueidentifier = NULL,
@CodNivel uniqueidentifier = NULL,
@Prioridade tinyint = NULL,
@Destacar varchar(10) = NULL,
@UtlIdBPI uniqueidentifier = NULL,
@DataInsercao datetime = NULL,
@Texto text = NULL,
@CodInteraccao uniqueidentifier OUTPUT,


When i have a text to set into @Texto parameter wich size is above 8kb the parameter CodInteraccao returned to VB isn't the right one. In VB i get an uniqueidentifier, but not the one that was created in database.If the text is below 8kb all works fine. The returned guid is the right one.

Does anyone have an ideia about this problem.



I’m using “Microsoft ActiveX Data Objects 2.5 Library” for managing database & tables. I want to create some database and tables at run time, so I also checked “Microsoft ADO Extension 2.1 for DLL and Security” in reference table. But if this two item checked together, there will be some errors in the program (i.e. in defining Columns for Data Grid). I want to create .mdb tables and also I want to use Data Grid. I can define database & table by using DAO and there is no problem, but with ADO how can I create them?


ADODB Connection
I've downloaded some code to see how databases can be used adn there is a line in the code that says:

Dim cnDB As ADODB.Connection

This application that I downloaded works fine, however when I try this line in my own code it tells me that an object is required - or I get user defined type not defined.

Please help!


More ADODB Problems
OK this time I have compared all the references and I cannot find anything that is different but on one application that I have downloaded it creates a database using this:

Public Sub CreateDataBase()
Dim objcat As adox.Catalog
Dim objMyTable As New adox.Table

Set objcat = New adox.Catalog

objcat.Create strConnection
objcat.ActiveConnection = strConnection

although when I try this I get:

runtime error '-2147467262 (80004002)':
No such interface supported.

Thank very much for your help and I do apologise for being a dumb * * *!


ADODB In Module
hi guys! i want to use ADODB in module but an error occurs, "User defined type not Defined"! is there any way i can use ADODB in Module?!

Old ADODB Works; New Does Not
I'm re-writing a little bit of VB6 code, trying to clean it up as an exercise to learn how the flow of the old code is working.

The old code uses "RecordCount" - so, why can't I? Should I be using something else?

The old code looks like this:
Private Sub cmdHR_Click()
Dim rsDept, rsPrint, rsMgr As ADODB.Recordset
sqlCmd.CommandText = "SELECT * FROM MGRSPV where [GROUP]='PRODUCTION'"
Set rsMgr = sqlCmd.Execute()
Hfmt = 0
rsMgr.Filter = "[SHIFT] = '1'"
sMgrSft(0) = UCase(rsMgr("MGR/SPVSR"))
rsMgr.Filter = "[SHIFT] = '2'"
sMgrSft(1) = UCase(rsMgr("MGR/SPVSR"))
rsMgr.Filter = "[SHIFT] = '3'"
sMgrSft(2) = UCase(rsMgr("MGR/SPVSR"))
rsMgr.Filter = "[SHIFT] = '4'"
If rsMgr.RecordCount > 0 Then sMgrSft(3) = UCase(rsMgr("MGR/SPVSR")): Hfmt = -1
The new code looks like this:
Public Sub HeaderSub(nPage As Single, sGroup As String)
Dim n As Integer
Dim strSql As String
Dim rsMgrA, rsDept, rsPrint As ADODB.Recordset
Hfmt = 0
For n = 0 To 3
sqlCmd.CommandText = strSql + sGroup + "' AND [SHIFT]='" + CStr(n + 1) + "'"
rsMgrA = sqlCmd.Execute()
' breaks below saying obj doesn't support this method (RecordCount)
If (rsMgrA.RecordCount > 0) Then
sMgrSft(n) = UCase(rsMgrA("MGR/SPVSR"))
If (n = 3) Then Hfmt = -1
End If
End Sub
Also, can I use format strings in VB6? Are they called format strings?

Copyright © 2005-08, All rights reserved