Problem with ADO in VB6
ja...@smkzone.com
I've been working with Visual Basic 6 for some time now, but I'm new
to ADO. I'm working on a Time Clock application for a business and
trying to create a database called "Employees.mdb" and populate 3
comboboxes on the main form with employee's names with the names of
the employees when the form loads.
The code I have creates the database file, but apparently it is empty,
because when I run the program, I get "Run-time error '
-2147217864(80040e37'; The Microsoft Jet database engine cannot find
the input table or query 'Employees'. Make sure it exists and that its
name is spelled correctly."
I've pasted the code from the module and from the form load sub. Could
someone please look at it and tell me how to fix it? Thanks!
Jason
------------------------------------
Module code
------------------------------------
Option Explicit
Public DBPath As String
Public Cat As ADOX.Catalog
Public Dog As ADOX.Table
Public Bird As ADOX.Column
Public Fish As ADOX.Index
Public Mouse As ADODB.Connection
Public Hamster As ADODB.Recordset
Public Gecko As ADODB.Command
Public Sub MakeDB()
On Error GoTo ErrMakeDB
DBPath = App.Path & "\Employees.mdb"
Set Cat = New ADOX.Catalog
Cat.Create "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & DBPath & _
";Jet OLEDB:Engine Type=5;"
Cat.ActiveConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & DBPath
Set Dog = New ADOX.Table
With Dog
.Name = "Employees"
.ParentCatalog = Cat
Set Bird = New ADOX.Column
With Bird
.Name = "Employee Name"
.ParentCatalog = Cat
.Type = adVarWChar
End With
.Columns.Append Bird
.Keys.Append "PrimaryKey", adKeyPrimary, "Employee Name"
Set Bird = New ADOX.Column
With Bird
.Name = "Social Security Number"
.ParentCatalog = Cat
.Type = adBigInt
End With
.Columns.Append Bird
.Keys.Append "PrimaryKey", adKeyPrimary, "Social Security Number"
Set Bird = New ADOX.Column
With Bird
.Name = "Employee Number"
.ParentCatalog = Cat
.Type = adInteger
.Properties("AutoIncrement") = True
End With
.Columns.Append Bird
.Keys.Append "PrimaryKey", adKeyPrimary, "Employee Number"
Set Bird = New ADOX.Column
With Bird
.Name = "Clocked In"
.ParentCatalog = Cat
.Type = adBoolean
End With
.Columns.Append Bird
.Keys.Append "PrimaryKey", adKeyPrimary, "Clocked In"
End With
Cat.Tables.Append Dog
Cat.Users.Append "Admin"
StartDB
Set Cat = Nothing
ErrMakeDB:
Dim fName As String
Dim sMsg As String
Dim fNum As Integer
fName = App.Path & "\error_log.txt"
sMsg = "[" & Now & "]: Error " & Err.Number & "(" &
Err.Description & ") in Sub MakeDB of modMain." & vbCrLf
fNum = FreeFile
Open fName For Append As #fNum
Print #fNum, sMsg
Close #fNum
End Sub
Public Sub StartDB()
On Error GoTo ErrStartDB
Set Mouse = New ADODB.Connection
DBPath = App.Path & "\Employees.mdb"
Mouse.Provider = "Microsoft.Jet.OLEDB.4.0"
Mouse.CursorLocation = adUseClient
Mouse.Mode = adModeReadWrite
Mouse.Open DBPath, "Admin"
MakeAdmin
ErrStartDB:
Dim fName As String
Dim sMsg As String
Dim fNum As Integer
fName = App.Path & "\error_log.txt"
sMsg = "[" & Now & "]: Error " & Err.Number & "(" &
Err.Description & ") in Sub StartDB of modMain." & vbCrLf
fNum = FreeFile
Open fName For Append As #fNum
Print #fNum, sMsg
Close #fNum
End Sub
Public Sub MakeAdmin()
Set Gecko = New ADODB.Command
With Gecko
.ActiveConnection = Mouse
.CommandText = "SELECT * FROM Employees;"
.CommandType = adCmdText
End With
Set Hamster = New ADODB.Recordset
With Hamster
.CursorType = adOpenStatic
.CursorLocation = adUseClient
.LockType = adLockOptimistic
.Open Gecko
.AddNew
.Fields(0) = "Admin"
.Fields(1) = "000000000"
.Fields(2) = "0000"
.Fields(3) = "Yes"
.Update
End With
Hamster.Close
Mouse.Close
Set Mouse = Nothing
Set Gecko = Nothing
Set Hamster = Nothing
End Sub
Sub Main()
Dim fPath As String
fPath = App.Path & "\Employees.mdb"
If FileExists(fPath) Then
Load frmTimeClock
frmTimeClock.Show
Else
MakeDB
Load frmTimeClock
frmTimeClock.Show
End If
End Sub
Public Sub StopDB()
Mouse.Close
Set Mouse = Nothing
On Error GoTo 0
Exit Sub
End Sub
Public Function FileExists(fPath As String) As Boolean
If Dir(fPath) <> "" Then
FileExists = True
Else
FileExists = False
End If
End Function
____________________
Form Code
-----------------------------------
Private Sub Form_Load()
DBPath = App.Path & "\Employees.mdb"
pbxClock.FontSize = 10
pbxClock.FontBold = True
Timer1.Interval = 250
Timer1.Enabled = True
mvwTimeClock.MultiSelect = True
mvwTimeClock.MaxSelCount = 14
mvwTimeClock.MinDate = Now
dtpStart.MinDate = Now
dtpEnd.MinDate = Now
Set Cat = New ADOX.Catalog
With Cat
Cat.ActiveConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & DBPath
End With
Set Dog = New ADOX.Table
With Dog
.Name = "Employees"
.ParentCatalog = Cat
End With
Set Gecko = New ADODB.Command
Set Mouse = New ADODB.Connection
Set Hamster = New ADODB.Recordset
Mouse.Provider = "Microsoft.Jet.OLEDB.4.0"
Mouse.CursorLocation = adUseClient
Mouse.Mode = adModeReadWrite
Mouse.Open DBPath
With Gecko
.ActiveConnection = Mouse
.CommandText = "SELECT * FROM Employees;"
.CommandType = adCmdText
End With
With Hamster
.CursorType = adOpenStatic
.CursorLocation = adUseClient
.LockType = adLockOptimistic
.Open Gecko
End With
Do Until Hamster.EOF
cmbName1.AddItem Hamster.Fields("Employee Name")
cmbName2.AddItem Hamster.Fields("Employee Name")
cmbName3.AddItem Hamster.Fields("Employee Name")
Hamster.MoveNext
Loop
End Sub
Jason Keats
> Hi all,
>
> I've been working with Visual Basic 6 for some time now, but I'm new
> to ADO. I'm working on a Time Clock application for a business and
> trying to create a database called "Employees.mdb" and populate 3
> comboboxes on the main form with employee's names with the names of
> the employees when the form loads.
>
> The code I have creates the database file, but apparently it is empty,
> because when I run the program, I get "Run-time error '
> -2147217864(80040e37'; The Microsoft Jet database engine cannot find
> the input table or query 'Employees'. Make sure it exists and that its
> name is spelled correctly."
>
> I've pasted the code from the module and from the form load sub. Could
> someone please look at it and tell me how to fix it? Thanks!
>
> Jason
I've never before had to attempt connecting to a Mouse to issue a Gecko
to retrieve a Hamster.
Once I was past that hurdle, I discovered that your MakeDB procedure
failed at "Cat.Tables.Append Dog". Error -2147217859 Type is invalid.
It therefore wouldn't surprise me if you had no Dog table in your
database. Trying to retrieve Employees from a non-existent table would
be futile.
If you have Access, I would try opening the database file to determine
what's there - in which case you may as well use Access to create your
tables, columns, indexes, etc.
If you don't have Access then you should seriously consider using a
different database, eg SQLite.
So, do you have Access? Do you have an Employees table? Do you have data
in the table?
When you've got your table creation code working I'll help with the rest.
Jason Keats
> Hi all,
>
> I've been working with Visual Basic 6 for some time now, but I'm new
> to ADO. I'm working on a Time Clock application for a business and
> trying to create a database called "Employees.mdb" and populate 3
> comboboxes on the main form with employee's names with the names of
> the employees when the form loads.
>
> The code I have creates the database file, but apparently it is empty,
> because when I run the program, I get "Run-time error '
> -2147217864(80040e37'; The Microsoft Jet database engine cannot find
> the input table or query 'Employees'. Make sure it exists and that its
> name is spelled correctly."
>
> I've pasted the code from the module and from the form load sub. Could
> someone please look at it and tell me how to fix it? Thanks!
The code you supplied fails to create the database properly.
Do you have Access? If not, you're making it hard for yourself choosing
to use a Jet (MDB) database.
Please fix your MakeDB procedure first - then worry about trying to
retrieve data.
http://www.oblique.ch/ms/ADOX_Faq.html
It's also a good idea to use variable names that others can understand.
ja...@smkzone.com
The only database program I have on this machine is Lotus Approach 97,
but it seems to read the Access files perfectly.
ja...@smkzone.com
Thanks!
The question is *how* do I fix it? All that came directly from online
tutorials! If it's not making the database file correctly, I don't
know *how* to fix it! LOL
J.
On Jan 16, 7:15 am, Jason Keats <jke...@melbpcDeleteThis.org.au>
wrote:
Jason Keats
> Jason,
>
> Thanks!
>
> The question is *how* do I fix it? All that came directly from online
> tutorials! If it's not making the database file correctly, I don't
> know *how* to fix it! LOL
>
> J.
Try something like the following...
'Module...
Option Explicit
Public gsDBPath As String
Public gsConnection As String
Public goCnn As ADODB.Connection
Public Sub Main()
gsDBPath = App.Path & "\Employees.mdb"
gsConnection = "Provider=Microsoft.Jet.OLEDB.4.0;Jet OLEDB:Engine
Type=5;"
gsConnection = gsConnection & "Data Source=" & gsDBPath & ";"
If Not FileExists(gsDBPath) Then
MakeDB
StartDB
MakeAdmin
StopDB
End If
Load frmTimeClock
frmTimeClock.Show
End Sub
Public Function FileExists(fPath As String) As Boolean
If Dir(fPath) <> "" Then
FileExists = True
Else
FileExists = False
End If
End Function
Public Sub MakeAdmin()
Dim rst As ADODB.Recordset
Dim cmd As ADODB.Command
Set cmd = New ADODB.Command
With cmd
.ActiveConnection = goCnn
.CommandText = "SELECT * FROM Employees;"
.CommandType = adCmdText
End With
Set rst = New ADODB.Recordset
With rst
.CursorType = adOpenStatic
.CursorLocation = adUseClient
.LockType = adLockOptimistic
.Open cmd
.AddNew
.Fields(0) = "Admin"
.Fields(1) = "000000000"
''.Fields(2) = "0000" 'can't insert an autonumber primary
key!
.Fields(3) = True
.Update
End With
rst.Close
Set cmd = Nothing
Set rst = Nothing
End Sub
Public Sub MakeDB()
Dim cat As ADOX.Catalog
Dim tbl As ADOX.Table
Dim col As ADOX.Column
Set cat = New ADOX.Catalog
cat.Create gsConnection 'create database
cat.ActiveConnection = gsConnection
Set tbl = New ADOX.Table
With tbl
.Name = "Employees"
Set .ParentCatalog = cat
Set col = New ADOX.Column
With col
.Name = "Employee Name"
Set .ParentCatalog = cat
.Type = adVarWChar
End With
.Columns.Append col
Set col = New ADOX.Column
With col
.Name = "Social Security Number"
Set .ParentCatalog = cat
''.Type = adBigInt 'causes error
.Type = adVarWChar
End With
.Columns.Append col
Set col = New ADOX.Column
With col
.Name = "Employee Number"
Set .ParentCatalog = cat
.Type = adInteger
.Properties("AutoIncrement") = True
End With
.Columns.Append col
.Keys.Append "PrimaryKey", adKeyPrimary, "Employee Number"
Set col = New ADOX.Column
With col
.Name = "Clocked In"
Set .ParentCatalog = cat
.Type = adBoolean
End With
.Columns.Append col
End With
cat.Tables.Append tbl
''cat.Users.Append "Admin" 'causes error
Set cat = Nothing
End Sub
Public Sub StartDB()
Set goCnn = New ADODB.Connection
goCnn.Open gsConnection, "Admin"
End Sub
Public Sub StopDB()
goCnn.Close
Set goCnn = Nothing
End Sub
'Form...
Option Explicit
Private Sub Form_Load()
' pbxClock.FontSize = 10
' pbxClock.FontBold = True
' Timer1.Interval = 250
' Timer1.Enabled = True
' mvwTimeClock.MultiSelect = True
' mvwTimeClock.MaxSelCount = 14
' mvwTimeClock.MinDate = Now
' dtpStart.MinDate = Now
' dtpEnd.MinDate = Now
Dim cnn As ADODB.Connection
Dim cmd As ADODB.Command
Dim rst As ADODB.Recordset
Set cnn = New ADODB.Connection
cnn.Provider = "Microsoft.Jet.OLEDB.4.0"
cnn.CursorLocation = adUseClient
cnn.Mode = adModeReadWrite
cnn.Open gsDBPath
Set cmd = New ADODB.Command
With cmd
.ActiveConnection = cnn
.CommandText = "SELECT [Employee Name] FROM Employees;"
.CommandType = adCmdText
End With
Set rst = New ADODB.Recordset
With rst
.CursorType = adOpenStatic
.CursorLocation = adUseClient
.LockType = adLockOptimistic
.Open cmd
End With
Do Until rst.EOF
'Debug.Print rst.Fields("Employee Name")
cmbName1.AddItem rst.Fields("Employee Name")
cmbName2.AddItem rst.Fields("Employee Name")
cmbName3.AddItem rst.Fields("Employee Name")
rst.MoveNext
Loop
rst.Close
Set rst = Nothing
Set cmd = Nothing
cnn.Close
Set cnn = Nothing
End Sub
I've cleaned up the code so it makes more sense (to me at least).
See the comments I added in the module code for the causes of the errors.
It's usual to have the primary key first (not third) in the list. I'd
move it (Employee Number) to the top - and rename it EmployeeID.
It's also unusual to have spaces in column names.
I still don't know why you'd want three comboboxes with the same
employee name in them on your form.
There are plenty more improvements that could be made to the above code.
For example, you could try putting the code in a Class.
HTH
ralph
>Jason,
>
>Thanks!
>
>The question is *how* do I fix it? All that came directly from online
>tutorials! If it's not making the database file correctly, I don't
>know *how* to fix it! LOL
>
The basic problem is, as Mr. Keats points out, in creating the initial
database. I'm not exactly sure why it is failing, but have a few
guesses - concerning the lifecycle and state of your various objects.
But no matter - here is some background information and suggestions
(with a few editorial opinions <g>) on how to fix it.
[And will also explain why Mr. Keats' suggestions are good advice.]
ADOX, the ADO extension library, was added to ADO as an
"after-thought". When ADO was introduced it included no DDL
capabilities. The reason being ADO was to provide "universal data
access", and since all databases supported widely different DDLs, MS
felt developers would be using the 'native DDL' libraries or server
command queries for their respective databases.
The 'native' DDL library for Jet is DAO.
This made sense for databases that provided a server, such as SQL
Server, because you can create or modify database schemas by simply
querying the database server using ADO. This didn't work for flat file
databases, such as Jet formatted MDBs, because there was no server to
talk to. This didn't bother MS too much as they preferred everyone to
migrate to SQL Server anyway. <g>
However, Jet was too popular and developers using Jet loudly and
bitterly complained. After-all, MS was proclaiming that ADO was to be
the *complete* replacement of DAO and *the only modern way* of working
with databases - therefore some kind of "ADO Jet DLL" was needed. In
answer MS quickly clobbered together the ADOX library. It is thus
important to note - that while ADOX is an ActiveX component, and uses
OLE DB and OLE DB providers - it is NOT ADO. It is an entirely
separate library, its library model is different, and it doesn't work
quite the same. (ie, sometimes familiarity with ADO is a detriment to
working with ADOX. <G>)
I went into all this in order to explain why ADOX often appears to be
a little quirky, if not down-right flaky at times. <g> If you need to
perform DDL services for a Jet formatted database then DAO is the
superior library. It provides more DDL features, is more robust, and
definitely faster than ADOX. So that is one way out of your dilemma.
You can use DAO and ADO side-by-side in the same program. Just make
sure that you fully qualify all database objects (eg, ADODB.Recordset
- DAO.Recordset), and of course, never try to chew on the same items
from both libraries at the same time.
However, the predjudice against DAO is so strong, and developers being
what they are, you will likely want to stay with "ADO" all the way
through and use ADOX in spite of the issues involved.
So. The first thing to do is to completely separate ADOX and ADO
operations and procedures, ie, do DDL - get out - then do SQL. Isolate
each step in the process as much as you can. This solves the major
problem - trying to chew on objects before their time, as well as
making it easier to debug and modify. (Note if you use DAO you get
this kind of 'separation' for free. <g>)
This is the primary reason your code, borrowed from various working
examples, appeared to not work when combined together.
[Note: In what follows I'm not saying this is obsolutely necessary,
only that if you follow these suggestions your final product is more
likely to work (be less flaky and provide fewer surprises. <g>)]
First off, don't even keep ADOX and ADO object reference variables in
the same namespace. This removes any temptation to start hammering on
the same object with both libraries.
a) Create the database. Then quit. Clean-up the object.
b) Re-open the database, create your catalog, create your schema, then
quit. Clean-up the objects.
[Note: This is can be especially important when setting up
Column/Field attributes. Some properties can not be set in ADOX until
a valid Column/Field of the required type exists. So you may need to
make this a two-step process. as well.]
c) Then create your ADODB objects and proceed.
So go back and refactor your code using the above suggestions (and
those from Mr. Keats), and all should be well.
hth
-ralph
ralph
wrote:
Need to correct something ...
>
>However, Jet was too popular and developers using Jet loudly and
>bitterly complained. After-all, MS was proclaiming that ADO was to be
>the *complete* replacement of DAO and *the only modern way* of working
>with databases - therefore some kind of "ADO Jet DLL" was needed. In
>answer MS quickly clobbered together the ADOX library. It is thus
>important to note - that while ADOX is an ActiveX component, and uses
>OLE DB and OLE DB providers - it is NOT ADO. It is an entirely
>separate library, its library model is different, and it doesn't work
>quite the same. (ie, sometimes familiarity with ADO is a detriment to
>working with ADOX. <G>)
>
I mistyped the term "ADO Jet DLL". I meant to type "ADO Jet DDL".
But the term is still misleading. While it was predominately the
out-cry from Jet developers that led to an "ADO DDL" extension
library, there was also a belief that there should be a "universal
data definition" library model if for no other reason than to provide
symmetry with "universal data access". So ADOX was developed with the
idea of providing a tool to work with any data source, not just "Jet".
ADOX is dependent on an OLE DB provider to implement the ADOX library
model, just as ADO is dependent on its respecitive providers. Back
when ADOX was introduced there were often mild glitches and quirks
depending on which Provider (and database) was being used. This is
less a problem today since both the ADO and ADOX libraries and
providers have matured and are more robust, but something to consider
when reading older articles or using sample code from the web. Be
aware that code that works for one combination may need a bit of
massage to work with another.
-ralph
ja...@smkzone.com
In regards to your question about 3 combo boxes with the same
info...lol. The first lets the employee clock in, the second is for
adding, editing, and deleting employees from the database, and the
third is for tallying up and printing out employees' time on the
clock.
J
On Jan 17, 6:14 am, Jason Keats <jke...@melbpcDeleteThis.org.au>
wrote:
ja...@smkzone.com
but says "Could not find installable ISAM." It happens at the
Cat.Create line.
On Jan 17, 6:14 am, Jason Keats <jke...@melbpcDeleteThis.org.au>
wrote:
Access Developer
> . . . This is less a problem today since
> both the ADO and ADOX libraries and
> providers have matured and are more
> robust, but
Yep, they have matured so much that in what Microsoft considers "the real
world of application development" (DotNet) they have been replaced by
ADO.NET which shares with classic ADO and ADOX only the letters "ADO". It's
built on a different object model, and does not rely on OLEDB.
Larry Linson
Microsoft Office Access MVP
(and user of classic VB from V.1 - V.6)
ralph
And your contribution to the OP's question concerning "Problem with
ADO in VB6" is ... ?
-ralph
ralph
>The code didn't work! Now it gives me the same error code as before,
>but says "Could not find installable ISAM." It happens at the
>Cat.Create line.
>
You either have an error in then connection string ...
> gsDBPath = App.Path & "\Employees.mdb"
> gsConnection = "Provider=Microsoft.Jet.OLEDB.4.0;Jet OLEDB:Engine
> Type=5;"
> gsConnection = gsConnection & "Data Source=" & gsDBPath & ";"
>
Looks ok, but I wonder about that trailing ";". It is not needed.
I would get rid of the "OLEDB:Engine Type=5" property setting. Engine
type 5 is a Jet 4.0 database and it is the default for the Jet OLEDB
provider. I doubt that is a problem, but it is redundant.
... or ADO/Jet access libraries, drivers, and providers are not
installed or registered.
Here are a few quick ways to determine if ADO or Jet is even installed
on your system.
1) Open the "ODBC Data Source Administrator". If using Vista or
Windows 7, just type the above (or "Data Sources") in the Run box.
For previous platforms it is found in under Administration services
off the Control Panel. (This has to be enabled, and is not available
by default.)
On the first tab "User DSN" you should see several data source
drivers listed. Make sure you have one Named ...
"MS Access Database"
If not there then you likely do not have Jet installed or setup
properly on your machine.
2) Follow the instructions at ...
http://msdn.microsoft.com/en-us/library/e38h511e(v=vs.71).aspx
to create and then open the "Data Link Properties" dialog.
You should see at least two providers available:
A "Jet OLE DB for ODBC",
and a "Jet OLE DB provider"
The same for providers as for drivers. If they are not there then you
likely do not have ADO installed on your machine.
Note: With both dialogs if you have an existing Jet-formatted .mdb
file on your system, you can open and test various connection
combinations.
If you can't find any of the drivers or providers mentioned above, or
if when you test them you get additional or the same errors, holler
back.
If using a pre-XP Windows platform, you may need to download and
install the MDAC package for your platform. ADO and Jet is included
with Vista and Windows 7, although for some configurations are not
installed. We need to know what Windows version and SP you are using,
and also whether or not you have full-administration rights to the
machine before we can give an more detailed advise.
-ralph
ralph
wrote:
Oops!
>
>1) Open the "ODBC Data Source Administrator". If using Vista or
>Windows 7, just type the above (or "Data Sources") in the Run box.
>For previous platforms it is found in under Administration services
>off the Control Panel. (This has to be enabled, and is not available
>by default.)
I said off the 'control panel'. Actually it a folder off the start
menu. It is often not visible by default. You can enable it for older
systems by setting options on the task bar.
-ralph
ja...@smkzone.com
MS Access Driver *is* listed in my installed ODBC Data Sources, and I
am running XP Pro.
Thanks!
Jason
On Jan 17, 10:23 pm, ralph <nt_consultin...@yahoo.net> wrote:
> On Mon, 17 Jan 2011 22:18:40 -0600, ralph <nt_consultin...@yahoo.net>
ja...@smkzone.com
Microsoft Jet 3.51 OLE DB Provider
Microsoft Jet 4.0 OLE DB Provider
Microsoft OLE DB Provider for Indexing Services
Microsoft OLE DB Provider for Internet Publishing
Microsoft OLE DB Provider for ODBC Drivers
Microsoft OLE DB Provider for Oracle
Microsoft OLE DB Provider for SQL Server
Microsoft OLE DB Simple Provider
MSDataShape
OLE DB Provider for Microsoft Directory Services
So, basically, everything but a "Jet OLE DB for ODBC."
Jason Keats
> The code didn't work! Now it gives me the same error code as before,
> but says "Could not find installable ISAM." It happens at the
> Cat.Create line.
It worked for me. :-)
Maybe you should start with something simpler.
Create a new project containing only a module with the following...
'References:
' Microsoft ActiveX Data Objects 2.8 Library
' Microsoft ADO Ext. 2.8 for DDL and Security
Option Explicit
Public gsDBPath As String
Public gsConnection As String
Public Sub Main()
Dim sPath As String
sPath = App.Path
If Right$(sPath, 1) <> "\" Then sPath = sPath & "\"
gsDBPath = sPath & "Employees.mdb"
Debug.Print gsDBPath
gsConnection = "Provider=Microsoft.Jet.OLEDB.4.0;"
gsConnection = gsConnection & "Data Source=" & gsDBPath & ";"
Debug.Print gsConnection
CreateEmptyDB
End Sub
Public Sub CreateEmptyDB()
Debug.Print "CreateEmptyDB"
On Error Resume Next
Kill gsDBPath
On Error GoTo 0
Dim cat As ADOX.Catalog
Set cat = New ADOX.Catalog
cat.Create gsConnection 'create database
Set cat = Nothing
If Dir(gsDBPath) <> "" Then
Debug.Print "OK"
Else
Debug.Print "FAILED!"
End If
End Sub
What is the output of the Immediate window?
Is an empty Employees.mdb file created in the same folder as the above code?
ralph
The "Microsoft OLE DB Provider for ODBC Drivers" is essentially the
"Jet OLE DB for ODBC" provider. So you're good to go as far as having
the providers installed.
The Connection String is the next most likely candidate for the "Could
not find installable ISAM" error, so have you modified it to remove
the property and the trailing semi-colon?
If you have and you still get that error, then the next thing to check
is the registration and whether you might have mismatched or corrupted
components. This can be difficult to ferret out without some
experience with ADO, so the easiest method is to simply re-install the
latest 'n greatest components.
For installing MDAC 2.8 (Primarily the ADO libraries and providers)
...
http://www.microsoft.com/downloads/en/details.aspx?FamilyID=6c050fe3-c795-4b7d-b037-185d0506396c&displaylang=en
(Simply Google "MDAC Download")
This is enough for ADO/ADOX, but while you're at it, you might as well
download the Jet libraries too....
http://www.microsoft.com/downloads/en/details.aspx?FamilyID=2deddec4-350e-4cd0-a12a-d7f70a153156
(or Google "Jet Download")
Also you should consider downloading the Northwind database.
http://www.microsoft.com/downloads/en/details.aspx?familyid=c6661372-8dbe-422b-8676-c632d66c529c&displaylang=en
I realize you don't have MSAccess and the tutorial part is of little
interest, but the database itself is what MS supplies with its Office
products - so you have a nice known mdb file to play with. You can use
it to test you environment. And later on you can test code and play
with ADO by running against it.
After you have re-installed the ADO libraries (MDAC), and downloaded
the Northwind database. Then open the Universal Data Link (.udl) and
select the Jet 4 provider and test the connection. If all is still not
well, then we need to pursue more exotic possibilities. <g>
-ralph
Nobody
to create the MS Access database.
ja...@smkzone.com
already a part of Windows and the Jet Database is older than what's
already on my system, lol. This is frustrating!
Jason
> Also you should consider downloading the Northwind database.http://www.microsoft.com/downloads/en/details.aspx?familyid=c6661372-...
Access Developer
> And your contribution to the OP's
> question concerning "Problem with
> ADO in VB6" is ... ?
Same as I told the VB6 folk at user group meetings "back in the day", use
Access to create your database*, not DDL not ADOX, use DAO, long proven and
stable, to access the data, and avoid.
* You'll get a ROI on the first non-trivial
DB you define with the Access product.
You can do this and still use ADO, if you
feel compelled, but DAO is easier. You
can also use Access to create Queries
against your database and copy the SQL.
I figured you wouldn't like it, and thought with your superior experience
with ADO and ADOX that you could probably help the poster.
It just "turned over my tickle box" when you talked about all the post-VB6
development and fixes to ADO and ADOX and I couldn't resist. It's been a
long time since Microsoft introduced Visual Studio, and a long time since
they introduced ADO.NET and that is what has been enhanced and stablized.
ralph
>Apparently I've already updated those because it says the MDAC is
>already a part of Windows and the Jet Database is older than what's
>already on my system, lol. This is frustrating!
>
Ha. Frustrating but not hopeless.
You've come a long way.
1) You now know the core components for ADO and Jet are installed.
2) You now know you have the latest 'n greatest.
3) You now know there is something wrong with the install,
registration of those system components, and your code is likely not
at fault.
Perhaps, your VB Project is referencing the wrong component or
internally confused. <ha don't laugh, it happens>
Create a brand-new project that only invokes ADOX and creates an empty
mdb file and exits.
Run it ... what happens?
Have you tried using Data Link dialog (.udl) to test your ability to
connect to a known good mdb file?
Download the Northwind database?
Do you get an error when you try to do this?
I gave a quick way to test for the existence of data components by
using the ODBC and OLEDB database tools. Microsoft also supplies a
Component Checker -
http://support.microsoft.com/kb/307255/
From here on out, it is a simple matter of ferreting out the problem
whcih can be time consuming - but take heart it is nothing overly
complex or complicated - one or two components are missing, not
registered, or mismatched. Consider that "missing" in this context
means not only that the component is not on the box, but it might be
stored in a context that VB doesn't have the permissions to see it.
Something else to try after you have done the above. Is to simply
Google the error (don't bother trying to narrow the search down to
your specific environment - ADO is ADO no matter who is using it for
what), and then check-out the various replies. 90% of them will not
relate your situation, but one or two might provide a good resolution.
Also take heart, that once it is resolved - it is likely resolved for
forever - and you will never have to go through this again. Well, at
least not until it happens again. <G>
-ralph
ralph
<accd...@gmail.com> wrote:
>"ralph" <nt_cons...@yahoo.net> wrote
>
> > And your contribution to the OP's
> > question concerning "Problem with
> > ADO in VB6" is ... ?
>
>Same as I told the VB6 folk at user group meetings "back in the day", use
>Access to create your database*, not DDL not ADOX, use DAO, long proven and
>stable, to access the data, and avoid.
>
Ha. Then it might surprise you that I agree most heartly with the
comment - "use DAO".
ADO has definite advantages over DAO within a multi-user, distributed
environment, or if a developer plans to support different database
formats with the same code base, but when it comes to a local
Jet-formatted database file and one user - DAO is the superior data
access tool.
I too, have preached that since the release of ADO, but like I said it
tends to fall on deaf ears. <g>
> * You'll get a ROI on the first non-trivial
> DB you define with the Access product.
> You can do this and still use ADO, if you
> feel compelled, but DAO is easier. You
> can also use Access to create Queries
> against your database and copy the SQL.
>
>I figured you wouldn't like it, and thought with your superior experience
>with ADO and ADOX that you could probably help the poster.
>
>It just "turned over my tickle box" when you talked about all the post-VB6
>development and fixes to ADO and ADOX and I couldn't resist. It's been a
>long time since Microsoft introduced Visual Studio, and a long time since
>they introduced ADO.NET and that is what has been enhanced and stablized.
>
Yes. My comments can seem out-of-context when I said "the ADO and ADOX
libraries and providers have matured and are more robust" since
development essentially stopped on ADO with ADO 2.8, almost ten years
ago. (However, there have been some minor fixes and improvement over
that time - collimating in what are the now called DAC 6 components
supplied with newer Windows platforms).
I was referring to the fact, that if one researchs online for
information about ADO and VB6, they will often stumble across articles
that were written for earlier versions. VB6 itself, went from ADO 2.5,
to 2.6, 2.7 rtm, to a final plaform-specific ADO 2.8 (DAC 6). Some of
the earlier glitches and work-arounds mentioned are often no longer
applicable.
[You might find it interesting that if using Jet-formatted databases
with the .Net platform using ADO or even DAO is still a better
performer than ADO.Net.]
-ralph
GS
> [You might find it interesting that if using Jet-formatted databases
> with the .Net platform using ADO or even DAO is still a better
> performer than ADO.Net.]
Just to add to your comment...
'Jet' has been replaced by 'Ace' as of MSO v12 (2007). That means
Access DBs are no longer Jet-formatted. Don't know, however, what its
performance is with ADO. Does anyone know if DAO even works with it?
--
Garry
Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc
ja...@smkzone.com
said it was all good. Hmmmm.
On Jan 18, 2:42 pm, ralph <nt_consultin...@yahoo.net> wrote:
> On Tue, 18 Jan 2011 10:58:17 -0800 (PST), "ja...@smkzone.com"
>
> <ja...@smkzone.com> wrote:
> >Apparently I've already updated those because it says the MDAC is
> >already a part of Windows and the Jet Database is older than what's
> >already on my system, lol. This is frustrating!
>
> Ha. Frustrating but not hopeless.
>
> You've come a long way.
> 1) You now know the core components for ADO and Jet are installed.
> 2) You now know you have the latest 'n greatest.
> 3) You now know there is something wrong with the install,
> registration of those system components, and your code is likely not
> at fault.
>
> Perhaps, your VB Project is referencing the wrong component or
> internally confused. <ha don't laugh, it happens>
> Create a brand-new project that only invokes ADOX and creates an empty
> mdb file and exits.
> Run it ... what happens?
>
> Have you tried using Data Link dialog (.udl) to test your ability to
> connect to a known good mdb file?
> Download the Northwind database?
> Do you get an error when you try to do this?
>
> I gave a quick way to test for the existence of data components by
> using the ODBC and OLEDB database tools. Microsoft also supplies a
> Component Checker -http://support.microsoft.com/kb/307255/
ja...@smkzone.com
the database either.
On Jan 18, 2:40 am, Jason Keats <jke...@melbpcDeleteThis.org.au>
wrote:
Jason Keats
news:1352328a-3f1a-46b9...@m35g2000vbn.googlegroups.com...
No errors and nothing in the Immediate Window, but it didn't create
the database either.
I shouldn't have to point out that what you say is impossible - if you
followed my instructions.
Maybe you forgot to remove Form1 and set the startup object to Sub Main.
Did you put a break point in Sub Main and step through it?
Anyway, it's as I said in my first message - you should have Access if
you're going to attempt to write a program using a Jet database.
I would normally just distribute an empty database file with my
application - rather than write code to generate it.
However, if I did want to include code to generate the database then I have
a little VB6 application that will create the DAO (not ADOX) code for me -
given any MDB file. Why write it by hand if you don't need to? :-)
ralph
>ralph wrote on 1/18/2011 :
>> [You might find it interesting that if using Jet-formatted databases
>> with the .Net platform using ADO or even DAO is still a better
>> performer than ADO.Net.]
>
>Just to add to your comment...
>
>'Jet' has been replaced by 'Ace' as of MSO v12 (2007). That means
>Access DBs are no longer Jet-formatted. Don't know, however, what its
>performance is with ADO. Does anyone know if DAO even works with it?
*Replaced* in the sense that Jet 4.0 'replaced' Jet 3.x. The new
access engine is a Jet Red deriviate like all its predecessors. It not
only supports the new "Ace-format" (.accdb), but is also fully
compatible with all previous .mdb formats (except 1.0 and 2.0). It is
nothing more than a "Jet 5".
Providing a new name for the latest Jet-based database engine came
about for several reasons:
1) The Office Team took over development of "Jet" (red) from the SQL
Server Team.
(The other Jet Blue engines now tied to the o/s went to Windows Team.)
2) The previous Jet engines managed backward-compatiblity, and the
mixing and matching of different Jet-formatted files with different
Jet-engines, with various translators components. Rather than add to
the pile - MS wisely (and thankfully) decided to roll everything up
into one single 'core' engine.
3) They also cleaned up some of the data access middleware.
4) And Marketing probably just decided it was time. <g>
The new DAO for using ACE is "ACE DAO". (The component filename tells
the story - acedao.dll). The object model is identical to DAO, because
it essentially is DAO (4?).
You can use the new ACE file format and engine from VB6 using ADO, and
the ACE OLE DB provider ("Microsoft.ACE.OLEDB.12.0"). I'm not sure
about using ACE DAO in VB6. Don't believe there is any major reasons
why it wouldn't work, but haven't found the time to go play and find
out*. You won't, of course, find anything official from MS on the
subject because:
1) VB6 no longer exists as far as MS is concerned,
2) While the previous Jet engine was a "stand-alone" engine, the
Office Team intends ACE to be an "Office" resource only, and
3) MS really prefers developers to be using SQL Express for a local
database and SQL Server for anything bigger or shared.
As far as performance goes, it is the same as before with no
surprises. ACE DAO performs better and has access to more features
than ADO with the new ACE-format (.accdb).
-ralph
[*If you want to try it. Reference acedao.dll in a VB Project.]
GS
That's a great deal of interesting and informative content. While I've
used Ace in a few MSO VB6/VBA projects, I had no idea why it was
renamed NOR did I have any idea that it was still 'Jet' under the hood.
I've also heard of SQL Express but know absolutely nothing about it.
Where can I find more info for using it with VB6?
ralph
>Thanks Ralph!
>
>That's a great deal of interesting and informative content. While I've
>used Ace in a few MSO VB6/VBA projects, I had no idea why it was
>renamed NOR did I have any idea that it was still 'Jet' under the hood.
>
>I've also heard of SQL Express but know absolutely nothing about it.
>Where can I find more info for using it with VB6?
"Using SQL Server 2005 Express from Visual Basic 6"
http://msdn.microsoft.com/en-us/library/bb264566(v=sql.90).aspx
Some general notes:
(But Note, I am not a SQL Express expert. So tale this with a grain of
salt. <g>)
1) SQL Express and it predecessors (msde, compact...) were design
around an engine that had one major requirement. It was to look and
feel as much like SQL Server as possible. The engine was developed by
an outside group that did an incredible job. So if you do decide to
use SQL Express - articles and information on TSQL and other SQL
Server mechanics will often apply.
2) The above means that if you do decide to move SQL Server most of
your code will survive without change.
3) You are limited to using ADO. There are other more direct routes,
but they are not available to VB, as far as I know.
4) Stick with SQL Express 2005. The newer versions are even more
joined with .Net.
-ralph
Nobody
news:mDrZo.8193$gM3...@viwinnwfe01.internal.bigpond.com...
> I shouldn't have to point out that what you say is impossible - if you
> followed my instructions.
It's possible if he is creating the database in a place that he doesn't have
write access to.
Jason Keats
Not true. He said there was no output to the Immediate Window. That's
impossible if he actually ran the code I provided.
Henning
"ralph" <nt_cons...@yahoo.net> skrev i meddelandet
news:8hpcj6t7cgju56sup...@4ax.com...
And if your'e like me, no .net except in a VBox, use MySQL.
/Henning
ja...@smkzone.com
it! lol
Beginning to think I may have a glitchy install of Visual Studio
itself (either that, or this machine is possessed, rotfl!, because I
got the dratted thing working right finally. The database is created
and the name "Admin" appears in the combo boxes, as needed. But I had
to go through some funky steps to get there! First, I solved that "No
installable ISAM by changing the value of gsConnection back to the
original connection string I had in the first place (the one that GAVE
me the darn error I came here to ask for help with in the first
place!) When I did that, it gave me errors stating that the string
variables used in the CommandText argument of the Command weren't
valid SQL statements (and yes, I copied and pasted your code directly
into VB, Jason, lol). So I changed them to the proper SQL statements
and got errors because I didn't use those string variables. So I
changed them back and, exasperated at this point, saved and closed out
VB altogether. Then, on a hunch, I opened it up again, ran the
program, and VOILA! It worked perfectly!
Of course, I now have a whole new set of ADO questions for you! :)
Now that I have the database created, in order for an employee to
clock in by either typing or selecting his name from cmbName1 or,
alternatively, by entering his Employee Number into the textbox
immediately under cmbName1, called txtNumber1, I need 3 things to
happen.
1. When the name is selected from or typed into the combo box, the
employee's record in the database is accessed and the Employee Number
from the "Employee_Number" field is displayed in the txtNumber1.
2. Conversely, if the employee enters his number, his name is selected
in cmbName1.
3. If no record is found matching the text of either cmbName1 or
txtNumber 1, a MsgBox pops up informing the user no such record
exists.
I know how to make a MsgBox. How do I do the rest of it?
Thanks again!
Jason
On Jan 19, 4:38 am, Jason Keats <jke...@melbpcDeleteThis.org.au>
wrote:
Henning
In Sub MakeDB(), Exit Sub is missing before ErrMakeDB:
In Sub Sub StartDB(), Exit Sub is missing before ErrStartDB:
So the error handler code will always run.
/Henning
<ja...@smkzone.com> skrev i meddelandet
news:d44e69bb-768a-41ef...@w17g2000yqh.googlegroups.com...
Jason Keats
>
> Of course, I now have a whole new set of ADO questions for you! :)
>
> Now that I have the database created, in order for an employee to
> clock in by either typing or selecting his name from cmbName1 or,
> alternatively, by entering his Employee Number into the textbox
> immediately under cmbName1, called txtNumber1, I need 3 things to
> happen.
>
> 1. When the name is selected from or typed into the combo box, the
> employee's record in the database is accessed and the Employee Number
> from the "Employee_Number" field is displayed in the txtNumber1.
>
> 2. Conversely, if the employee enters his number, his name is selected
> in cmbName1.
>
> 3. If no record is found matching the text of either cmbName1 or
> txtNumber 1, a MsgBox pops up informing the user no such record
> exists.
>
If I were you, I would first learn to add the primary key to the
combobox - so that it can be used in a search for the record in the
database...
cmbName1.Clear
Do Until rst.EOF
cmbName1.AddItem rst.Fields("Employee Name").Value
cmbName1.ItemData(cmbName1.NewIndex) = rst.Fields("Employee Number").Value
rst.MoveNext
Loop
cmbName1.AddItem "<Select an Employee>", 0
cmbName1.ListIndex = 0
I've removed the code for the other two comboboxes!
Then when you click on the combobox, cmbName1, you probably want to
display the "Employee Number"...
Private Sub cmbName1_Click()
If cmbName1.ListIndex < 0 Then Exit Sub
txtNumber1.Text = cmbName1.ItemData(cmbName1.ListIndex)
End Sub
Here's a command button to retrieve the associated data for the above
selected (or user entered) Employee Number...
Private Sub btnGetEmployee_Click()
Call DisplayEmployee(CLng(Val(Me.txtNumber1.Text)))
End Sub
The above calls something like...
Private Sub DisplayEmployee(ByVal lEmployeeNumber As Long)
If lEmployeeNumber <= 0 Then Exit Sub
Dim cnn As ADODB.Connection
Dim cmd As ADODB.Command
Dim rst As ADODB.Recordset
Dim sSQL As String
sSQL = "SELECT * FROM Employees WHERE [Employee Number] = " &
lEmployeeNumber
'Debug.Print sSQL
Set cnn = New ADODB.Connection
cnn.Open gsConnection
Set cmd = New ADODB.Command
With cmd
.ActiveConnection = cnn
.CommandText = sSQL
.CommandType = adCmdText
End With
Set rst = New ADODB.Recordset
With rst
.CursorType = adOpenStatic
.CursorLocation = adUseClient
.LockType = adLockOptimistic
.Open cmd
Debug.Assert .RecordCount = 1
Debug.Print "Employee Number: " & .Fields("Employee Number").Value
Debug.Print "Employee Name: " & .Fields("Employee Name").Value
Debug.Print "Social Security Number: " & .Fields("Social
Security Number").Value
Debug.Print "Clocked In: " & .Fields("Clocked In").Value
.Close
End With
Set rst = Nothing
Set cmd = Nothing
cnn.Close
Set cnn = Nothing
End Sub
I hope that's enough to get you started.
Eventually you will want to put most of your data access code into a
class (to avoid repeating yourself), and perhaps start using
disconnected recordsets. But those lessons are for another day.
ralph
<comput...@coldmail.com> wrote:
>> 4) Stick with SQL Express 2005. The newer versions are even more
>> joined with .Net.
>>
>> -ralph
>
>And if your'e like me, no .net except in a VBox, use MySQL.
>
>/Henning
>
You don't know how hard it was to NOT add somthing like the following
to my list:
5) SQL Express is an interesting database, but IMHO, if you want to
migrate away from a Jet database, then MySQL or SQLite would be a
better choice for VB6.
MySQL
http://dev.mysql.com/downloads/
SQLite
http://www.sqlite.org/
http://www.sqlite.org/cvstrac/wiki?p=SqliteWrappers
but wanted to stick to topic.
-ralph
GS
Thanks Ralph and Henning! Sorry to branch OT! I really appreciate your
input.
I've already looked into SQLite due to having Olaf's RichClient pkg,
which includes a wrapper for using SQLite.
I've also heard of MySQL but not looked into it as a possible
alternative for Jet DBs because I understood it to be closely related
to SQL Server. Hhmm.., time to revisit some areas!
Access Developer
> [You might find it interesting that if
> using Jet-formatted databases
> with the .Net platform using ADO
> or even DAO is still a better
> performer than ADO.Net.]
That's very interesting... as you probably gathered, I am not a big fan of
DotNet.
ralph
>
>I've already looked into SQLite due to having Olaf's RichClient pkg,
>which includes a wrapper for using SQLite.
>
>I've also heard of MySQL but not looked into it as a possible
>alternative for Jet DBs because I understood it to be closely related
>to SQL Server. Hhmm.., time to revisit some areas!
Are you possiblibly thinking of SQL Server's 'mommy' - Sybase??
MySQL is an Open Source product and is not related to SQL Server in
any way. However devotees of open source being what they are, often
praise MySQL as a non-vendor cost-effective replacement for SQL
Server.
It also tends to have a "look 'n feel" like SQL Server - but then all
RDBMS tend to look like. (Well except for Oracle. <g>)
I get along with MySQL and it works well for small shops, but it does
have scalablity issues. That is my only complaint.
-ralph
Access Developer
> 3) You are limited to using ADO. There
> are other more direct routes, but they
> are not available to VB, as far as I know.
Isn't SQL Server Express ODBC-Compliant? If it is, then DAO (and there's
DAO for ACE's ACCDB/ACCDE as well as Jet's MDB/MDE) should work just fine on
tables linked from the ACCDB/ACCDE/MDB/MDE -- that's what the Access group
at Microsoft recommends as best practice now.
Henning
Hrmm, so Googles servers are "small shops"?? ;)
Unless you only reference the free version.
/Henning
ja...@smkzone.com
cmbName1.ItemData(cmbName1.NewIndex) = rst.Fields("Employee
Number").Value
Which is strange, because I opened the database in Approach and that
field IS there!
Jason
On Jan 19, 6:51 am, Jason Keats <jke...@melbpcDeleteThis.org.au>
wrote:
GS
>
>
>>
>> I've already looked into SQLite due to having Olaf's RichClient pkg,
>> which includes a wrapper for using SQLite.
>>
>> I've also heard of MySQL but not looked into it as a possible
>> alternative for Jet DBs because I understood it to be closely related
>> to SQL Server. Hhmm.., time to revisit some areas!
>
> Are you possiblibly thinking of SQL Server's 'mommy' - Sybase??
No, ..never heard of that one! I was under the impression (however
false) that MySQL, SQL Express, and SQL Server were all M$ related
products. You have to bear in mind that I've been using delimited text
files for storing data to use with my M$O Excel addin apps. I could use
MDBs but then they're M$ and require I use Jet or Ace right now. I'm
looking to get away from M$ dependancies!<g>
I have the ability to replace my Excel addins with pure VB6 apps using
Farpoint's Spread control, and thus my interest in dbase alternatives
for VB6. Using text files is adequate (for now) but some clients may
want to use a proper database at some point.
>
> MySQL is an Open Source product and is not related to SQL Server in
> any way. However devotees of open source being what they are, often
> praise MySQL as a non-vendor cost-effective replacement for SQL
> Server.
So I see after browsing your links. I've downloaded the documentation
for now, so I can read up on it.
>
> It also tends to have a "look 'n feel" like SQL Server - but then all
> RDBMS tend to look like. (Well except for Oracle. <g>)
Interesting since MySQL comes from Oracle!
>
> I get along with MySQL and it works well for small shops, but it does
> have scalablity issues. That is my only complaint.
>
> -ralph
--
Henning
"GS" <g...@somewhere.net> skrev i meddelandet
news:ih7kuo$6gp$1...@news.eternal-september.org...
eat small fish...
/Henning
Henning
/Henning
<ja...@smkzone.com> skrev i meddelandet
news:9c77a348-9dee-412d...@d8g2000yqf.googlegroups.com...
Henning
I promis this will be the last OT post, at least in this thread... ;)
From the manual for MySQL 4.0
Scalability and Limits:
a.. Handles large databases. We use MySQL Server with databases that
contain 50 million records. We also know of users who use MySQL Server with
60,000 tables and about 5,000,000,000 rows.
/Henning
ralph
>I get a run-time error 3265 here:
>cmbName1.ItemData(cmbName1.NewIndex) = rst.Fields("Employee
>Number").Value
>
>Which is strange, because I opened the database in Approach and that
>field IS there!
>
Try using square brackets.
rst.Fields("[Employee Number]").Value
I'm with Henning Field Names with spaces are going to bite you
everytime you turn around - everything from Select statements on down.
Go back and get rid of them. If for some reason you have to have the a
"full name" available - create an aditional Description or Caption
field.
-ralph
ja...@smkzone.com
brackets. Same results.
Access Developer
"Henning" <comput...@coldmail.com> wrote
> From the manual for MySQL 4.0
> Scalability and Limits:
>
> a.. Handles large databases. We use
> MySQL Server with databases that
> contain 50 million records. We also
> know of users who use MySQL Server with
> 60,000 tables and about 5,000,000,000 rows.
I can't imagine a normalized, relational database with 60,000 _tables_.
Larry
ralph
<comput...@coldmail.com> wrote:
>>
>> I get along with MySQL and it works well for small shops, but it does
>> have scalablity issues. That is my only complaint.
>>
>> -ralph
>
>Hrmm, so Googles servers are "small shops"?? ;)
>Unless you only reference the free version.
>
lol, I knew a MySQL fan would call me on that one. Yes I am referring
to what you can download for free.
One of the great strengths, and a great weakness, of Open Source
software is "you" own it. You become your own vendor, and while plenty
of information is often available (for popular items), you also become
your own support staff. You get the source - you can enhance it,
customize it, just plain have a ball. But you own every change you
make.
Google's "MySQL" isn't what you are going to get from the public
download. You can match what they have - IF - you get yourself a staff
of of developers, a couple of million, and chew on it for a couple of
years. <BG>
-ralph
ralph
<ja...@smkzone.com> wrote:
>... First, I solved that "No
>installable ISAM by changing the value of gsConnection back to the
>original connection string I had in the first place (the one that GAVE
>me the darn error I came here to ask for help with in the first
>place!) ...
Can't resist reminding you that I pointed out a ways, that an erronous
connection string was the *Next* most common reason for that error.
>
>3. If no record is found matching the text of either cmbName1 or
>txtNumber 1, a MsgBox pops up informing the user no such record
>exists.
>
I wouldn't use a MsgBox. They get annoying after a while.
Place an 'error label' over or below the entry field and toggle its
visibility to signal invalid entries.
If you must use a MsgBox, create your own dialog form or splash
screeen with a timer to automatically close after 5-10 seconds.
-ralph
ralph
<comput...@coldmail.com> wrote:
>
>I promis this will be the last OT post, at least in this thread... ;)
>
>From the manual for MySQL 4.0
>Scalability and Limits:
>
> a.. Handles large databases. We use MySQL Server with databases that
>contain 50 million records. We also know of users who use MySQL Server with
>60,000 tables and about 5,000,000,000 rows.
>
Another great strength of Open Source is the loyality of its user
base.
'Raw' numbers like that for databases are meaningless, and especially
when reported from major users, as we know all Open Source is highly
'tweakable'. (See other post) It depends on the over-all schema, what
you are doing, how often, and by how many.
[You might be surprised at the 'raw' numbers that are posted for
Jet-formatted files.]
All modern RDBMS (mostly due to significant hardware upgrades) exhibit
limits that appear almost astronomical to what was available just a
few years ago. But the reality is always some place far below those
numbers. In my experience - MySQL slows down dramatically as size and
complexity of the schema increases. Much sooner than SQL Server will
for a comparable circumstance.
But then I doubt the OP is planning on abusing MySQL any where near
those limits. So he should be fine.
-ralph
Henning
To both of you.
D_mn, didn't take long to brake my promise... ;)
Right, "normal" usage never goes over a fraction of what is availble.
I just can't help that I've fallen in love with it. One reason is that I
don't need to install the framework. :))
/Henning
ralph
[SQL Server Express part]
"Should" is right.
I would like to see someone do it (connect to SQL Express using the
DAO 3.6 access library). I unfortunately don't have access to a VB6
box at the moment.
It is ODBC-compliant, and I have personally connected using
DirectODBC, and MFC DAO, but something went wrong when attempting to
connect with DAO 3.6 from VB6. Don't remember the issue now, but I
agree there isn't any reason why it *shouldn't* work. I probably
shouldn't have said "limited to ADO", but rather something like "I
know ADO works". <G>
Why don't you give it a try?
[ACE/JET DAO part]
As for best practice, it is my understanding that they recommend using
DAO 3.6 for Jet (4.0 and below), and ACEDAO for ACE.
As a sidenote, there is an interesting little 'trick' .Net uses when
connecting to ACE/Jet databases. With .Net you can use the "DAO"
classes to write your code, but when the data file is opened it is
determined whether the format is ACE or Jet. If ACE then ACEDAO is
used, if Jet then DAO 3.6 is used. As both object models are identical
most programmers are unaware they are actually using two different
access libraries.
-ralph
ralph
<ja...@smkzone.com> wrote:
>Replaced all the spaces with underscores ...
Good boy. You've made us all very happy. <lol>
> ... and tried the square brackets. Same results.
Not good.
Well, all I can say is that error is one of the few that always means
exactly what it says - using that index/key a Field object could not
be found in the Fields collection.
[Recordsets are variant collections of collections of variants. <?? or
whatever I was trying to say. <bg>]
Go back over your code. Make sure you are chewing on what you think
you are.
Try using Ordinal Field numbers, or even the Bang syntax ('!').
-ralph
Nobody
news:00a4b00f-be32-46ab...@k13g2000vbq.googlegroups.com...
> Replaced all the spaces with underscores and tried the square
> brackets. Same results.
Try:
rst![Employee Number]
There is no ".Value" after it. If the field didn't have spaces, you could
have written it as:
rst!EmployeeNumber
Nobody
> brackets. Same results.
Also, try this code:
Dim fld As ADODB.Field
Debug.Print "Fields Count = " & rst.Fields.Count
For Each fld In rst.Fields
Debug.Print "fld.Name = '" & fld.Name & "'"
Next
Post the result here.
ja...@smkzone.com
fld.Name = 'Employee_Name'
ja...@smkzone.com
ja...@smkzone.com
to the MakeAdmin Sub. It returned all 4 fields in the immediate
window. But for some reason, it returns only the one field in the
Form_Load sub where I'm populating the comboboxes.
On Jan 19, 7:38 pm, "Nobody" <nob...@nobody.com> wrote:
Jason Keats
> Just to be sure, I deleted the database and added this same code below
> to the MakeAdmin Sub. It returned all 4 fields in the immediate
> window. But for some reason, it returns only the one field in the
> Form_Load sub where I'm populating the comboboxes.
Are you still using:
SELECT Employee_Name FROM Employees;
?
You should now be using:
SELECT * FROM Employees;
or
SELECT Employee_Number, Employee_Name FROM Employees;
to retrieve the data for your comboboxes.
ja...@smkzone.com
Now, if I can figure out how to make it check to see if the record
exists when a name is typed in the cmbName1 or a number into
txtNumber1, I'll be set (for now, anyway! LOL :))
Jason
On Jan 20, 1:24 am, Jason Keats <jke...@melbpcDeleteThis.org.au>
wrote:
ja...@smkzone.com
error for every single keystroke lol
ja...@smkzone.com
Same topic, new question, new problem.
The following code for the txtNumber1_Change sub works properly...
UNTIL txtNumber1 is cleared, at which point it gives me "Run-time
error -2147217900(80040e14): Syntax error (missing operator) in query
expression '[Employee_Number]=." I'm assuming its because
txtNumber1.text is now = " ". So, how do I fix that?
Thanks again!
Jason
-------------------
Private Sub txtNumber1_Change()
Dim cnn As ADODB.Connection
Dim cmm As ADODB.Command
Dim rss As ADODB.Recordset
Set cnn = New ADODB.Connection
With cnn
.Provider = "Microsoft.Jet.OLEDB.4.0"
.CursorLocation = adUseClient
.Mode = adModeReadWrite
.Open DBPath
End With
Set cmm = New ADODB.Command
With cmm
.ActiveConnection = cnn
.CommandText = "SELECT * FROM Employees WHERE [Employee_Number]=" &
txtNumber1.Text & ";"
.CommandType = adCmdText
End With
Set rss = New ADODB.Recordset
With rss
.CursorType = adOpenStatic
.CursorLocation = adUseClient
.LockType = adLockOptimistic
.Open cmm
End With
If Not (rss.EOF And rss.BOF) Then
cmbName1.Text = rss.Fields("Employee_Name").Value
Else
Select Case txtNumber1.Text
Case Is = ""
cmbName1.Text = "Select Name"
lblWarning1.Visible = False
Case Else
lblWarning1.Visible = True
End Select
End If
rss.Close
Set rss = Nothing
Set cmm = Nothing
Nobody
news:2fc5d098-bfbf-4321...@m13g2000yqb.googlegroups.com...
> .CommandText = "SELECT * FROM Employees WHERE [Employee_Number]=" &
> txtNumber1.Text & ";"
Try:
.CommandText = "SELECT * FROM Employees WHERE Employee_Number=" &
Chr(34) & txtNumber1.Text & Chr(34)
ja...@smkzone.com
Jason Keats
> Same topic, new question, new problem.
>
> The following code for the txtNumber1_Change sub works properly...
> UNTIL txtNumber1 is cleared, at which point it gives me "Run-time
> error -2147217900(80040e14): Syntax error (missing operator) in query
> expression '[Employee_Number]=." I'm assuming its because
> txtNumber1.text is now = " ". So, how do I fix that?
>
Have another look at the code I gave you. It doesn't have that problem.
ja...@smkzone.com
expression" is what happens.
On Jan 21, 4:00 am, Jason Keats <jke...@melbpcDeleteThis.org.au>
wrote:
Jason Keats
> Copied and pasted directly into VB. "Data type mismatch in criteria
> expression" is what happens.
Nonsense.
Your code:
.CommandText = "SELECT * FROM Employees WHERE [Employee_Number]=" &
txtNumber1.Text & ";"
My code:
Private Sub btnGetEmployee_Click()
Call DisplayEmployee(CLng(Val(Me.txtNumber1.Text)))
End Sub
Private Sub DisplayEmployee(ByVal lEmployeeNumber As Long)
If lEmployeeNumber <= 0 Then Exit Sub
Dim cnn As ADODB.Connection
Dim cmd As ADODB.Command
Dim rst As ADODB.Recordset
Dim sSQL As String
sSQL = "SELECT * FROM Employees WHERE [Employee Number] = " &
lEmployeeNumber
etc.
But now that you've renamed your columns, that should be:
sSQL = "SELECT * FROM Employees WHERE Employee_Number = " &
lEmployeeNumber
Note that the two lines
Call DisplayEmployee(CLng(Val(Me.txtNumber1.Text)))
and
If lEmployeeNumber <= 0 Then Exit Sub
prevent the error you're talking about.
So, stop telling fibs.
Henning
If Not (rss.EOF And rss.BOF) Then
Don't use the _Change event, use:
Private Sub txtNumber1_KeyPress(KeyAscii As Integer)
Dim nr As Long
If KeyAscii = vbKeyReturn Then
nr = Clng(Val(txtNumber1.Text))
If nr > 0 Then
add rest of code here, use nr in your SQL query string
/Henning
<ja...@smkzone.com> skrev i meddelandet
news:2fc5d098-bfbf-4321...@m13g2000yqb.googlegroups.com...
Henning
Dim nr As Long
If KeyAscii = vbKeyReturn Then
/Henning
"Henning" <comput...@coldmail.com> skrev i meddelandet
news:ihbuet$7lr$1...@news.eternal-september.org...
ja...@smkzone.com
Same program, same topic, new problem.
I'm trying to get it so that when the fields of a record are changed,
the record is updated and:
1. If Employee_Name has changed, remove the old item from the combobox
lists
2. If it hasn't changed, but either the SSN or Employee_Number fields
have changed, update the itemdata for the appropriate field.
3. Update the comboboxes on the main form with the new information.
So far, all this does is change the listindex back to the original one
and make no changes whatsoever. I can't figure it out. Help!
Jason
Here's the code.
Private Sub EditRecord()
Dim cnn As ADODB.Connection
Dim cmm As ADODB.Command
Dim rss As ADODB.Recordset
Dim nr As Long
nr = CLng(Val(txtEmplNumber.Text))
If nr > 0 Then
Set cnn = New ADODB.Connection
With cnn
.Provider = "Microsoft.Jet.OLEDB.4.0"
.CursorLocation = adUseClient
.Mode = adModeReadWrite
.Open DBPath
End With
Set cmm = New ADODB.Command
With cmm
.ActiveConnection = cnn
.CommandText = "SELECT * FROM Employees WHERE
[Employee_Number]=" & nr
.CommandType = adCmdText
End With
Set rss = New ADODB.Recordset
With rss
.CursorType = adOpenStatic
.CursorLocation = adUseClient
.LockType = adLockOptimistic
.Open cmm
End With
If Not (rss.EOF And rss.BOF) Then
cmbEmployee.Text = rss.Fields(0).Value
txtSSN.Text = rss.Fields(1).Value
txtEmplNumber.Text = rss.Fields(2).Value
rss.Update
End If
If rss.Fields(0).Value <> rss.Fields(0).OriginalValue Then
Dim i As Integer
For i = 0 To cmbEmployee.ListCount - 1
If cmbEmployee.ListIndex < 0 Then
' cmbEmployee.ListIndex = cmbEmployee.List(i)
cmbEmployee.RemoveItem cmbEmployee.List(i)
cmbEmployee.AddItem rss.Fields(0).Value
cmbEmployee.ItemData(cmbEmployee.NewIndex) = nr
'With frmTimeClock
' .cmbName1.ListIndex = cmbEmployee.ListIndex
' .cmbName2.ListIndex = cmbEmployee.ListIndex
'.cmbName3.ListIndex = cmbEmployee.ListIndex
'End With
'With frmTimeClock
' .cmbName1.RemoveItem .cmbName1.ListIndex
' .cmbName2.RemoveItem .cmbName2.ListIndex
'.cmbName3.RemoveItem .cmbName3.ListIndex
'.cmbName1.AddItem rss.Fields(0).Value
'.cmbName2.AddItem rss.Fields(0).Value
'.cmbName3.AddItem rss.Fields(0).Value
'.cmbName1.ItemData(.cmbName1.NewIndex) = nr
'.cmbName2.ItemData(.cmbName2.NewIndex) = nr
'.cmbName3.ItemData(.cmbName3.NewIndex) = nr
'End With
End If
Next
Unload Me
End If
Else
If txtSSN.Text = "" Or txtEmplNumber.Text = "" Then
lblWarning.Caption = "One or more required fields is missing!"
lblWarning.Visible = True
End If
Henning
<ja...@smkzone.com> skrev i meddelandet
news:f0babd48-0e76-405f...@f30g2000yqa.googlegroups.com...
First thing my eyes stop on, is that you have not changed:
If Not (rss.EOF And rss.BOF) Then
to
If Not (rss.EOF Or rss.BOF) Then
What you have works if there is a hit on the query. But if there is no match
you will have:
rss.EOF = True, rss.BOF = False
True And False = False
Not False is True.
While for no match
rss.EOF = True, rss.BOF = False
True Or False = True
Not True = False
See the difference?
/Henning
ja...@smkzone.com
button with this sub just changes the listindex to the original entry
without updating the list with the new name at all.
Jason
On Jan 30, 4:26 pm, "Henning" <computer_h...@coldmail.com> wrote:
> <ja...@smkzone.com> skrev i meddelandetnews:f0babd48-0e76-405f...@f30g2000yqa.googlegroups.com...
Henning
<ja...@smkzone.com> skrev i meddelandet
news:f0babd48-0e76-405f...@f30g2000yqa.googlegroups.com...
'******* this does nothing, field values are not changed the obove reads
from the DB to (textboxes?).
> End If
> If rss.Fields(0).Value <> rss.Fields(0).OriginalValue Then
'******* what is rss.Fields(0).OriginalValue supposed to be
jbod...@yahoo.com
In other words, the sub is *supposed" to read like this in plain
english, lol:
"If a record is found, update it with the data from the data from the
combobox cmbEmployee and the two textboxes, txtSSN and txtEmplNumber.
If the Employee_Name Field was changed, remove the old name from the
cmbEmployee dropdown list , as well as in the three comboboxes on the
main form, and add the new one. If Employee_Name was changed, but not
Employee_Number, set the itemdata of the new name to the old entry's
value. In all other instances, if Employee_Number is changed from its
original value, set the itemdata of the combobox list items to the new
Employee_Number."
Jason
On Jan 31, 2:38 pm, "Henning" <computer_h...@coldmail.com> wrote:
> Inline
>
> <ja...@smkzone.com> skrev i meddelandetnews:f0babd48-0e76-405f...@f30g2000yqa.googlegroups.com...
Jason Keats
<snipped>
Have you sorted out your problems?
If not, please describe what it is you're trying to do.
jbod...@yahoo.com
Haven't sorted it out yet.
I have a dialog form in the program which appears whenever the user
presses either the "Add," "Edit", or "Delete" button on the main form
and executes a different sub depending on the caption of the dialog
box ("Add Employee," "Edit Employee", "Delete Employee.") The dialog
box has a Combobox control named cmbEmployee which is populated with
the list from cmbName2 on the main form and has the same ListIndex and
two textboxes, txtSSN and txtEmplNumber, which display the SSN field
and Employee_Number field of the record selected in cmbName2. So far,
so good. The problem comes when trying to execute the EditRecord
sub. I need to set it up so that all the following occurs:
1. If the user edits the Employee_Name field, I need the original name
removed from cmbEmployee.List and the new name added to the list,
along with the ItemData of the original entry. I then need this
update to appear in cmbName1, cmbName2, and cmbName3 on the main form
as well.
2. If the user edits both Employee_Name and Employee_Number, I need
both the name and itemdata changed in all the comboboxes to reflect
the changes made to the recordset.
3. If the user changes only the Employee_Number but not the
Employee_Name, then the ItemData of that entry needs to be changed on
all the comboboxes.
The problem seems to stem from the fact that if anything is typed into
cmbEmployee, its ListIndex becomes -1.
Hope that helps! :)
Jason
Jason Keats
Does that mean you've got "Add", "Edit" and "Delete" buttons which bring
up the dialog? I can understand showing a new form to add or edit, but
not to delete.
If you don't want to squeeze the above three button onto your form then
you might be able to get rid of the Delete button by using the Delete
key instead. You can also put an "<add new>" item in your combobox (up
the top) - which will allow you to remove the "Add" button. So, you
really only need an "Edit" button.
And, if you change the Style property of your combobox to "2 - Dropdown
List", then users won't be able to type anything - so you won't get the
ListIndex = -1 problem.
1. can only be done after you've learned to display the edit dialog and
save your changes.
2. You can only edit the Employee_Name, not the Employee_Number -
because the Employee_Number is a database generated (AutoNumber) primary
key. If you really wanted to change the Employee_Number then you would
have to delete the original record and insert a new one.
3. See the above.
If you want Employee_Number to be editable, then you can't use it as a
primary key. A primary key is really not meant to be seen or used by the
client - and is never meant to change.
Jason Keats
>
> 2. You can only edit the Employee_Name, not the Employee_Number -
> because the Employee_Number is a database generated (AutoNumber) primary
> key. If you really wanted to change the Employee_Number then you would
> have to delete the original record and insert a new one.
>
Sorry, the last sentence above is confusing the situation. I'll try again...
If your primary key is database-generated (an AutoNumber in Jet/Access),
then the user can't edit it or insert their own - so you shouldn't let
the user edit it in the user interface.
If you display an AutoNumber, then it should be in a label or a locked
or disabled text box - so the user can't edit it. There is, after all,
no point letting them edit something that can't be saved!
If you ended up using code like the following to generate the
Employee_Number column, then it is an AutoNumber column.
Set col = New ADOX.Column
With col
.Name = "Employee_Number"
Set .ParentCatalog = cat
.Type = adInteger
.Properties("AutoIncrement") = True
End With
.Columns.Append col
.Keys.Append "PrimaryKey", adKeyPrimary, "Employee_Number"
If you want your users to enter the primary key, then you will have to
make some changes to your database and code.
You would have to remove the lines:
.Properties("AutoIncrement") = True
and
.Keys.Append "PrimaryKey", adKeyPrimary, "Employee_Number"
to stop it being an AutoNumber column.
You would then have to create a unique index on Employee_Number - to
prevent duplicates being entered.
And, where you inserted a new record, you would have to handle any error
caused by trying to insert a duplicate Employee_Number value.
jbod...@yahoo.com
a textbox instead and reworking the code. It works now. But now I
have another problem. Since it isn't ADO-related, I'm posting it in a
new thread. :)
Jason