February 8, 2012 at 8:25 pm
Okay,
I've got an access 2007 FE connecting to a SQL 2008 BE. I seen several posts about connecting to the backend by something other than a DNS connection. So I know have several questions:
1) what is the best method to make the connections
2) I've got 20 tables in my DB. Do I need to create a single connection for the DB as a whole, or do I need to create 20 connections for each individual table.
Be gentle...I'm a full blown newb when it comes to this.
Thanks!
February 9, 2012 at 1:41 pm
I have been using this forever.
http://www.accessmvp.com/DJSteele/DSNLessLinks.html
I can't say its the best but it has worked well for me. It easy to relink your tables if you add a field to a table or make other changes server-side. You don't need to worry about deploying any DSN's.
I can't I'm well versed in the subject, since I have always used this without having to dive in too deeply.
Good luck.
February 10, 2012 at 7:24 am
I can 2nd Chrissy321's approach as valid. I'm using something very similar. One piece of advice (is discussed also in article referenced by Chrissy321) is to locally store the tabledef info. before you start deleting connections. Other than that, this method works well for distributed front-ends. If you also put in a check to see if the user that is trying to use the application is a valid user (need some sort of table of valid users), then this is also a good way to prevent unauthorized use, as you can disconnect all linked tables and leave the app. unusable with little to no information about the data.
February 13, 2012 at 8:14 am
Thanks for the replies and advice. I looked at the link you provided Chrissy and kind of get what's going on, but where does that code need to be placed and when should it be called?
February 13, 2012 at 10:28 am
Create a new module and add everything from code start to code end. Save the module. I call mine modDNSLessConnection.
You'll see the module tab along with the other Access objects, tables, views, etc.
Create a second module. I call this modDNSLessConnectionDocumentation. This I refer to to run the code to update the tables.
In the second module is just place notes as shown below.
'These are instructions to update the linked tables when changes are made to the tables
' In the Visual Basic Window hit Cntrl-G to open the immediate window
'Enter the following: FixConnections "YourDatabaseServer", "YourDatabase"
'Change the server and database name as appropriate
'Hit Enter in the the immediate window
After you hit enter and the code has run the cursor will blink in the immediate window.
If you created a DSN to initially create your links, you should be able to delete the DSN and have the linked tables still work.
If its working you should be able to modify your table server-side, run the code, and then see the change in the linked table.
Give it a shot...
February 13, 2012 at 10:51 am
Apologies to Chrissy for butting in again, but to add:
On all of my applications, I have a "Splash" screen to announce certain information to the user at startup. I set up the application to open that form when the application starts. I place a call to the DNSless code module (as per Chrissy's suggestion, it is in a separate code module) in the form's "Open" event. That's where I do all of my preliminary checking for user permissions, security, database property initialization, and reconnecting to tables, setting up indexes (if necessary).
Hope that helps.
February 13, 2012 at 10:53 am
Stephen_W_Dodd (2/13/2012)
Apologies to Chrissy for butting in again, but to add:On all of my applications, I have a "Splash" screen to announce certain information to the user at startup. I set up the application to open that form when the application starts. I place the DNSless code in the form's "Open" event. That's where I do all of my preliminary checking for user permissions, security, database property initialization, and reconnecting to tables, setting up indexes (if necessary).
Hope that helps.
snap..:-)...properly coded it will work well
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
February 13, 2012 at 12:03 pm
Create a new module and add everything from code start to code end. Save the module. I call mine modDNSLessConnection.
You'll see the module tab along with the other Access objects, tables, views, etc.
Create a second module. I call this modDNSLessConnectionDocumentation. This I refer to to run the code to update the tables.
In the second module is just place notes as shown below.
'These are instructions to update the linked tables when changes are made to the tables
' In the Visual Basic Window hit Cntrl-G to open the immediate window
'Enter the following: FixConnections "YourDatabaseServer", "YourDatabase"
'Change the server and database name as appropriate
'Hit Enter in the the immediate window
After you hit enter and the code has run the cursor will blink in the immediate window.
If you created a DSN to initially create your links, you should be able to delete the DSN and have the linked tables still work.
If its working you should be able to modify your table server-side, run the code, and then see the change in the linked table.
Give it a shot...
February 13, 2012 at 12:46 pm
Thanks again. I'll give it a shot this week and let you know how I make out.
February 13, 2012 at 12:52 pm
No need for apologies J Livingston SQL. Your method is certainly more sophisticated than mine and is a method I will certainly consider.
February 13, 2012 at 12:56 pm
Chrissy321 (2/13/2012)
No need for apologies J Livingston SQL. Your method is certainly more sophisticated than mine and is a method I will certainly consider.
Stephen made the original comment... I just happened to see the post and said "snap"...cos thats what I do as well ......:-D
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
February 14, 2012 at 8:05 am
Uhhhh....in the code where am I supposed to put the server name and database name? Am I supposed to uncomment the input lines and add the info there or somewhere else?
February 14, 2012 at 8:14 am
In the immediate window paste in:
FixConnections "YourDatabaseServer", "YourDatabase"
Then hit enter.
February 14, 2012 at 8:24 am
I'm not sure where I'm supposed to type that into. I've got a VB window open and all the code cut and pasted from the link you provided, but not sure how to make it run or even access it once I save it.
February 14, 2012 at 8:28 am
You need to open the immediate window press CTRL+G it or access it from the View menu.
The copy and paste the Fixceonnections bit and hit enter.
Viewing 15 posts - 1 through 15 (of 26 total)
You must be logged in to reply to this topic. Login to reply