Wrong Version of Office 2k / VBA 6.0?

[dwelch35]

Hello Everyone,

I suspect I'm trying to accomplish something which is not supported by the version of Office / VBA that I'm currently using. Here's the details:

Office Version: 2000 Professional SP-2
VBA Version: 6.0.8435

I'm trying to create an ODBC connection in VBA in order to replace some Microsoft Queries embedded in a spreadsheet with something a little more dynamic. However, when I attempt to create a workspace, VBA gives me an error ("User defined type not defined&quot. I suspect that I will need Office 2k Developer edition in order to be able to create this connection, but I haven't been able to find any documentation to confirm this.
If anyone out there knows if my conclusion above is correct, I'd appreciate hearing from you! Thanks in advance!

 

[vb5prgrmr]

Actually it sounds like you need to set a reference to the object you want to use. In this case it sounds like DAO.

Good Luck

 

[dwelch35]

Thanks for the response! I went a little further and actually copy/pasted the following code in an effort to test my theory:

<Begin Code>
Sub OpenDatabaseTest()
Dim wsODBC As DAO.Workspace
Dim db As DAO.Database
'
' Create the ODBCDirect workspace
'
Set wsODBC = DBEngine.CreateWorkspace( _
Name:=&quot;ODBCWorkspace&quot;, _
UserName:=&quot;sa&quot;, _
Password:=&quot;&quot;, _
UseType:=dbUseODBC)
'
' Connect to the database
'
Set db = wsODBC.OpenDatabase( _
Name:=&quot;Northwind Sample Database&quot;, _
Options:=dbDriverNoPrompt, _
ReadOnly:=False, _
Connect:=&quot;ODBC;DSN=Northwind;UID=;PWD=;&quot
'
' Display the database name. Notice that the Name property is
' the same as the string specified in the OpenDatabase method's
' Name argument.
'
MsgBox db.Name
'
' Shut everything down
'
db.Close
wsODBC.Close
Set wsODBC = Nothing
Set db = Nothing
End Sub
<End Code>

Obviously I've changed the database information such as dsn to match what I'm trying to connect to, however the compiler doesn't even get past the first statement:

Dim wsODBC As DAO.Workspace

It's still giving me the same error message &quot;User defined type not defined&quot;. I think this should narrow down the possibilities to 1> The code I've copied in here is incorrect, in which case I need to abuse the publisher of the book I took it from, or 2> My version of Office / VBA doesn't support this type.
I went into VBA help and entered DAO. DAO shows up as a Key Word in the index, but every topic listed yeilds no help results. Let me explain what I mean by no help results: Dozens of topics are listed (eg RecordCount Property, Requery Method, CreateDatabase Method, etc), but regardless which one I choose, no help text is displayed for that topic to the right of the topics list (where the help text normally displays).
It seems I can obtain VBA help on most other subjects, except where DAO is part of the topic, which is very strange to me since I would assume it wouldn't show the topic at all if the help text wasn't there. As an experiment I tried looking in help on another user's PC running the same version of Office, but apparently they don't have VBA help installed at all.

Perhaps I should approach my original question from another angle. Has anyone been able to utilize a DAO with Office 2000 professional?

Thanks again for the response and I apologize for the long-winded message.

 

[vb5prgrmr]

In the code window for excel goto the menu>tools>references. A dialog will popup and you need to select Microsoft DAO 3.6 Object Library and hit OK.

This should solve your problem.

Good Luck

 

[dwelch35]

That did it! Thanks vb5prgrmr!