We help IT Professionals succeed at work.

Access 2000 - SQL 2000 Standard - SQL-DMO

ImraneA
ImraneA asked
on
Hi there

Problem - Erroe message "Runtime error '-2147165949 (8004d903) [Microsoft][ODBC SQL Driver]You must upgrade your SQL Enterprise Manager and SQL-DMO(SQLOLE) to SQL Server 2000(SQLDMO) to connect to this server."

Installed
Server - BackOffice 2000 (SQL 2000 Standard)
Client - Win 98 2nd edition

What am I doing wrong ?
Thanks

Code:

Sub call_list_tables()
Dim srvrname As String
Dim dbsname As String

'name of server and database
'call table lister
srvrname = "IMRANE"
dbsname = "Northwind"
Call list_tables(srvrname, dbsname)
End Sub

Sub list_tables(srvrname As String, dbsname As String)
Dim srv1 As New SQLDMO.SQLServer
Dim tab1 As New SQLDMO.Table

'connect to server

srv1.Connect srvrname, "sa", ""

'list table name and column count for user-defined tables
Debug.Print "User-defined tables with their column counts for the " & _
dbsname & " databases are : "
For Each tab1 In srv1.Databases(dbsname).Tables
    If tab1.TypeOf = SQLDMOObj_UserTable Then
        Debug.Print tab1.Name & String(25 - Len(tab1.Name), "") & _
        tab1.Columns.Count
    End If
Next
End Sub


Comment
Watch Question

CERTIFIED EXPERT

Commented:
are you certain your MDAC is up to date?

http://www.microsoft.com/data/download.htm


a better question though is what you are trying to do exactly?

dovholuk

Author

Commented:
1.Yes - installed it on client + server side.
2.Trying to learn SQLDMO via Access 2000.
CERTIFIED EXPERT

Commented:
> 2.Trying to learn SQLDMO via Access 2000.

ok. but is there a greater purpose or is it acedemcic?

i pasted your code into an access 97 db.  your code was fine except for one small issue, in your String() call you have a zero length string ("") at the end, you need this to be either a character (a space " " for instance). you can put Null in there, but i don't think that's what you're looking for.. what are you using the String() function for? was it in the example?

i digress... i then connected to my sql server at work. using northiwind here's what i got back.

User-defined tables with their column counts for the Northwind databases are :
Categories               4
CustomerCustomerDemo     2
CustomerDemographics     2
Customers                11
dtproperties             6
Employees                18
EmployeeTerritories      2
Order Details            5
Orders                   14
Products                 10
Region                   2
Shippers                 3
Suppliers                12
Territories              3

so it seems that with that ONE small modification, your code was fine.

here's a MSKB article which basically says install the sp1 for office 2000

http://support.microsoft.com/support/kb/articles/Q269/8/24.ASP

try installing the sp for O2k and see if it'll work for ye.

dovholuk
CERTIFIED EXPERT

Commented:
any luck ImraneA?

dovholuk

Author

Commented:
Sorry for not responding earlier, been side-tracked.  Be working on this problem this week and hopefully get back to you this week.

Author

Commented:
OK
My background is in Access 97/VBA - so want to know how you upsize database to SQL2000/Access 2000.  Set-up a network in the house.

Using a book Professional SQL Server Development with Access 2000 by Rick Dobson.  At Page 116.

Added a blank space:-
1.But receive new error message
"[Microsoft][ODBC SQL Driver][SQL Server]Login failed for user"Admin_Northwind"

Given access to this table (insert, update etc..) to above user.

Where am I going wrong ??
CERTIFIED EXPERT

Commented:
>My background is in Access 97/VBA - so want to know how
>you upsize database to SQL2000/Access 2000.

this is pretty easy to answer but it depends on how much vba code you have and how you've implemented it. the easy answer is simply to link all your tables and continue life as normal and happy... it's really that easy.

however depending on if you use a lot of .edit, .update, .openrecordsets etc, you might need to make minor modifications to your code. this could be a very minor undertaking (if you've little code) or a very MAJOR undertaking if you have lots of code.

i'd still like to know what you're trying to accomplish. i've been developing db's with acces97 as a front end for years and i've never needed to use SQL-DMO. theoretically, you should never need to edit the tables in your app. that's why i'm confused as to what you're trying to accomplish.

as for your latest issue, the logon failure, the user specified "Admin_Northwind" must be a valid user. is it? from here it'll get a little difficult to pinpoint.

if this doesn't help you. post the code you are using (again please) which only deals with connecting to your database and i'll take another look.

dovholuk

Author

Commented:
Classification
1.What I am trying to do is go threw above mentioned book -with all examples etc..  So, I can understand different methods that can be used.

2.Most of my databases have large amount of VBA code.  Most stored via modules e.g. 20 - 240 pages of code per form.  Using full features of Access 97 in terms of recordsets, batch transaction processing etc, pessimetic locking, security features etc..

Bottom line, wish to know all different methods that can be used in SQL2000 to Access 2000 situration.

Problem
1.The above example I used was from the book.  Just wanted to see if it worked.  Instead obtain above error message.

2.I created another user "Admin_Northwind" rather than "sa" to see if I get same error message.  For both users, I get same error message.

Do you follow ?

Answer to your Question
1.Yes
2.Using same code but different user.
3.Given permissions.

Question
I was reading somewhere there maybe a problem with security between Office 2000 + SQL 2000.  Not sure !  Could that be problem here ?

Tried
1.Installing service pack 2 (have installed service pack 1 - no problems), but obtain error messages.  Have you had any experence of this ?

ImraneA
CERTIFIED EXPERT

Commented:
i'll take another look today. sorry for the delay...

dovholuk
CERTIFIED EXPERT

Commented:
have you looked at the following? about half way down you'll see "RESOLUTION" in big bold letters. start reading from there... it may be a solution to your issue. i'm pretty sure you'll want to install the "SQL Server 2000 Client Tools/Files" that follows...

http://support.microsoft.com/support/kb/articles/Q269/8/24.ASP

good luck. let me know if this procedure helps you out! :)

dovholuk
CERTIFIED EXPERT
Commented:
any luck so far?

did any of these things help out? if not. you can delete the question and get your points back...

dovholuk

Author

Commented:
I am having difficulty trouble shooting this problem - working on other stuff.  Time being, to be fair, you have provided good pointers.  So grade as normal.

Later date, may have further queries, resubmit this again.

Thanks.

Explore More ContentExplore courses, solutions, and other research materials related to this topic.