Change where the access '03 looks at the information on the SQL '08

[54.69.6d.20]

I just started with this company on not sure how it's setup yet.

I have a test environment which has everything that the real environment has. I was looking to change the SQL DB name (long story on why). I changed it in the SQL server management. Now it doesn't connect. Changed the ODBC connection to reflect my change and still doesn't connect

Looking at the code in Access, i'm not sure where to start. I've been looking on google for an answer on where to change it in the access DB, but got no where. I see a lot of the tables are pointing to that SQL db. Just can't seem to find "where" to change it for the whole access mdb

Somebody, anybody???

 

[Pat Hartman]

Use the linked tables manager to relink the tables.

 

[54.69.6d.20]

Use the linked tables manager to relink the tables.

I looked at that (still in the beginning stage of access), but didn't think anything of it. I'm actually importing the DB again. I figured that will fix the issue.

 

[Pat Hartman]

Yes, deleting all the linked tables and relinking them will fix the problem, but the linked tables manager allows you to switch data sources on the fly so to speak. Just check the "always prompt for new location" button at the bottom of the dialog. You can then switch from ServerA to ServerB by changint the DSN you point to.

 

[54.69.6d.20]

Yes, deleting all the linked tables and relinking them will fix the problem, but the linked tables manager allows you to switch data sources on the fly so to speak. Just check the "always prompt for new location" button at the bottom of the dialog. You can then switch from ServerA to ServerB by changint the DSN you point to.

Thanks!!! Actually it had a couple of fails doing to my way and didn't change the DSN. Did it your way and it works!

Now it's time to clean the house. This access DB is been changed and patched by like 5 people.. Plus hate how they utilize it. Wondering why they didn't make a Windows App front end (VB.NET). Last "IT" guy said that he put permissions on it so that people wouldn't have access to certain things.. LOL at him and showed that just because he set "permissions" on the Employee table. Doesn't mean that no one can access it and change it, WHILE looking at company data. You wouldn't know of a "Converter" that will turn the access'03 front end to VB.NET? I can't find anything on it. Just don't want to have to invent the wheel again.

Again Thanks for your help.

 

[Pat Hartman]

The fact that they've gotten this far with no professional at the helm speaks volumes about the usability of Access and now you want to take that away from them Well, you're on your own. You'll need to build the .net app from scratch.

You can easily convert the tables to SQL server though and that will allow you to secure the data without rewriting the FE.

Way too many people confuse Access with Jet/ACE. In fact "Access" isn't a RDBMS at all. Jet and ACE (for A2007 and A2010) are the RDBMS. Access can work with any RDBMS that publishes an ODBC driver and if you use good techniques and don't use anything specific to only one RDBMS, you can swap backends on a whim. Try that in .net.

 

[54.69.6d.20]

Oh they had a guy that had stole the program from another company and just knew enough to change it and make it work for what they needed. They of course didn't know about this, until after. Then they have had a few other people that made changes. Now I'm playing house keeper because the db is over 45 gb and they are not using most of that.

I was looking to make it more secure. Right now if someone knew a little, they could mess up the whole database. Good thing I make sure backups run Right now they can't even delete an old user because it will mess up whatever that person did. Not sure WHY, but I'm thinking that they had setup NULL on the users. BUT.. I'm still learning this process.

I wanted to upgrade them to Access 2007 or 2010 because it offers more security (which is what I've read). They said that they tried it and it doesn't work. Would you know why? I'm looking at creating a new Vserver today and test it.

 

[Pat Hartman]

I wanted to upgrade them to Access 2007 or 2010 because it offers more security (which is what I've read).

I would delete my links to this source. If anything they offer less security since ULS (user level security) was deprecated in A2007. If it is data security that troubles you, the best solution is to upsize to SQL Server. It will almost certainly slow down the app (SQL Server is actually slower than Jet/ACE at low levels of data usage) but by modifying the forms to bind them to queries that limit the data selected, you can get back to the pre-upsizing speed.

"Doesn't work" - what kind of problem statement is that?

 

[54.69.6d.20]

I would delete my links to this source. If anything they offer less security since ULS (user level security) was deprecated in A2007. If it is data security that troubles you, the best solution is to upsize to SQL Server. It will almost certainly slow down the app (SQL Server is actually slower than Jet/ACE at low levels of data usage) but by modifying the forms to bind them to queries that limit the data selected, you can get back to the pre-upsizing speed.

"Doesn't work" - what kind of problem statement is that?

That's pretty much what I said to the other IT guy. He said, "It doesn't work" and that he couldn't get it to work. I asked, "What kind of issues where you having?" He changed the subject after that. I asked the boss and he told me the same thing. I said, "Let me guess. **** said that it doesn't work." So I'm guessing that it's never been done.

Security does bother me a little. Just because someone can just hit Window and Unhide the DB, make changes to their employee profile (to give them more access) and save. Also not knowing that they had done this. There's also a couple of forms that give company financial information. Not cool

 

[54.69.6d.20]

Now I'm thinking about using WebPages instead, but not sure how well that would work on the network and how to create what they use in access already with permissions.. hmmm...