I have recently upsized an Access 2000 project to SQL server 2000. during the Upsize I chose a to create a new Database and leave it with an access front end. It now resides as an MDB file with linked ODBC Tables. I’m not sure if this is the best configuration but it is blazing fast in a multi user environment compared to how access was performing.

Some problems have occurred since the upsize:

1. I can’t add a new table to the DB, even though I have added it to the backend SQL server instance. When it looks for an ODBC connection it doesn’t show our SQL server the way it did when we use Visual studio, or even the upsizing wizard, it seems to be looking for some connection file. Any idea on how to properly add tables to the access MDB file?
2. All the original queries still work in the MDB file, yet if I try to make a new view using the EM when you add a table to the project none of the fields show up, it only says’ (* all fields). If you force the issue and do it by code the query will, because it can’t find any fields.
If you actually select all the fields, and basically a table it will return all the values as normal with the proper titles for all the columns.
3. I have read Rob Vierra’s programming SQL server 2000, and when going back and looking at the views I created in pubs or Northwind they all seem to work. If I recreate them or different ones they are all fine. Could there be a setting that the upsize wizard sets that causes a problem?
4. I have also upsized the same DB to an ADP file which now acts like SQL server with the “Da Vinci tool set” instead of the usual “Access tools”, when creating new Queries or views the same problem occurs as with SQL server. I have imported all the old forms into the MDB version of our DB and have distributed it in our office which seems to work. But it does seem to take away a lot of the powers of views sprocs, and UDF’s that are played up as the strong point of SQL server.

Thanks in advance for any advice you might pass on.
-Roni


Roni Estein
[email protected]
https://www.e-drugsCanada.com

__________________
Roni Estein
[email protected]
https://www.e-drugsCanada.com

  How many users do you have? this is important. I use Windows authentication and I create a File DSN. Also within the File DNS use the UNC instead of the mapping of the shared folder. You use this DSN to link the tables and views. You add the new table with Enterprise manager.


  Are you refrewhing the links after every change to each view? if not, you should refresh them.

 

Create your own views in SQL Server. The wizard is not as good at creating the views. Actually for me, it has not created a view from an access query yet, so I am not sure what you are saying.

Let's take it one step at a time. What exactly is the problem with the tables?
How does it show your server in visual studio? .net or 6.0?






Sal

Hi Sal,

 

There are currently four users. We are running a windows 2000 Network with currently only my own laptop using windows XP pro so it can attach to the domain. There may be a need to increase this in the next 6 months. As for DSN’s UNC’s that still out of my league… but I read fast. I should also mention that currently and I know this is a sin among sins I also set the other users to the dbo role so they could get full utility from the DB.

 
I refresh the links after any change I make, and the changes are reflected in the DB with no problem. The diagram seems to be fine so the communication is there.

 
If I try to create a new view in the SQL server instance of this DB, each table I add will only show one checkbox with the select all fields in it. Now this is not in the Access MDB file anymore, but actually on the server using the EM! I’m sure I have permission because I am on the machine working with the DB. Also, if I go through the same process and create a view for pubs or northwind on SQL server aswell. I am just selecting a different DB, on the same server, those views work fine.

  In visual studio everything shows up fine. I cannot make a new view there, but I also cannot make a new view in pubs or any other DB. A note worthy issue, the new table I created in the EM shows up and can be looked at in Visual studio, but I still don’t know how to attach it in the MDB file DB I am having everyone work with.

Much appreciated
-Roni


Roni Estein
[email protected]
https://www.e-drugsCanada.com

Don't do that....
Someone could delete a table. Man, I have had such dumb users (but without them I would have no career), ans this users will delete data just to make sure that their "report" or "spreadsheet" is correct.

Create roles for security. It is more work up front, but it pays off quickly. With roles you can give them permissions to do anything they need and they get full functionallity fromm the database. Security is there for a reason. In fact, I believe that security is the first step in development. Many will disagree with me, but I stick to this religiously.

When you start developing, you must know who will be doing what. This comes from flow charts or notes or anything else that has been documented. You use all of these materials to create roles, for you it will be easier because you do not have many users, but you can not make everyone a dbo, unless the are all developers.

Now, to which diagram are you referring to?

You are getting only the *(All Columns) in enterprise manager? Are you hitting the correct server? Have you tried opening the database with the Access ADP? if yes, what results do you get?







Sal

Hi Sal,

I promise I'll change the security issue soon.
Yes I am getting the *(All Columns in enterprise manager. I actually sit and work on the computer that is running SQL server. I know it goofy, and when I was working through all the practise examples everything was hunky dory, and it it still is on those DB's. Its only when I try to make a new view on the upsized Database that the problem starts.

I can make a new table with no problem, but the MSb file can't attach to it. The way I did it originally is I upsized the DB told it to make a new Database, but not a new project. Therefore no ADP. What it did is attached all the new tables to my current open project, and then changed the name of all the old tables (the ones that were local) to TableName_local. I then deleted all the local tables, and lo and behold everything worked at 10 times the speed. It still does.

Now I also have a development SQL server on my laptop from when I was learning the basics and doing all the reading. Visual studio from any other computer can attach to both there "servers" and pick up the databases. Also I upsized the original Access DB again(one of the 5000 or so I have for safe keeping) on my laptop to work at home aswell. I know, I'm sick. This second one was upsized into an ADP project. I imported all the forms reports modules and macros into this adp project, but it won't take queries. So I went to create a new query, and instead of the usual access query interface, I get the SQL server 2000 EM interface. It also has the same problem where it will not let me add or refer to any specific fields in the tables. Yet it will acknowledge and let me choose from the available original tables.

I thought it might be a bad install, but when I go to master, or pubs, or and other DB I can create views. This leads me to wonder if there is a fundamental problem with the upsizing Function in Access! Does it set some specific detail in the upsize process? Is there some specific things to select or not that may make a difference?

I am baffled. I haven't been able to find anything about it on the net, nor does Rob V cover this in his book.

-Roni

Roni Estein
[email protected]
https://www.e-drugsCanada.com

Try creating a new SQL Server database and then go ahead and upgrade your database to that new database. I am not sure as to what is going on, but I think it may have something to do with having created so many different databases. You may be pointing to the wrong one.
Now, just in case. What type of security are you using? is it Windows authentication or SQL Server (where you have to use a username and password.) You may not have enough permissions to view all items in the database.

If let's say that you are using SQL security as a dbo, but you are using Windows NT authentication in EM, you will not see the items (maybe will not see the entire database). If this is the case, create a new connection to the server ans use SQL Server authentication, or have someone make your windows login a dbo login.

Let me know if this is the case.



Sal

Hi Sal,

I am a domain admin, which should give me rights to just about everything, yet I am not an admin on that local machine. I will try to create a new DB and upsize directly to it, Another step may be creating the whole DB in SQL server then unsing DTS to move all the information over. But the problem I thought might occur is the identity fields would all be reset. I'm not entirely sure of that. though.

-Roni

Roni Estein
[email protected]
https://www.e-drugsCanada.com

DTS is an option. Identity fields will not be reset. I believe that you can set them to number and once the data is in you can set them to identity. Make sure that you are using the proper security and the right server (I have done that myself).



Sal

Hi Sal,

I have got it working. It was the dumbest thing so anyone reading this don't do this! I had a period in the same of the MDB file. The Nameing conventions we were using have the name of the project and a version number to keep track of any changes and documentation. this version was "XXXXXXv0.7.mdb". When it was upsized using eith form it all still works and SQL server never throws an error and will partially function, but causes some bizzarre stuff to happen in the scripting. On a whim we did a backup and restore to a different name and everything works again. Needless to say I think graceful failing in software is a terrible concept and wastes so much time.

Thanks for all your help sal, I'm sure I'll need it more as we make the SQL server transition.

Roni Estein
[email protected]
https://www.e-drugsCanada.com