Thank you Jim for the reply. I am not sure how to do that. How can I do it through VBA. Is it a specific option while establishing the connection?
Hi weluru,
Using an mdb file for interacting with SQL Server relies on what is termed a 'connection string' it is essentially a combination of several elements using ODBC. (ODBC you will see in control panel...administrative tools...data sources. If you have a look at that you will see or should see the option to create file or system DSN,s as they are called that establish a connection to SQL Server using an ODBC driver.)
You can 'hook' into an SQL server database and retrieve datasets using what is called 'passthrough' queries (which end up looking like tables in Access represented by an icon of the 'world' globe.) The tables are never actually in Access they are merely 'linked' to access by the Access program in order for you to see those tables.
Now you may simply wish to process data ON THE SERVER directly itself WITHOUT retrieving datasets as well. Lets imagine you simply wish to empty out a specific table or update a particular column of a database table without ever returning the table to access itself. YOu do this by using TRANSACT SQL language (SQL servers own language speak directly within the SQL window in Access. Confusing isnt it SQL as used by access then VBA and now transact SQL all in the one microsoft access package hmmm :))
To ATTACH tables to Access from SQL Server You visually do this this by using the menubar command in the database window File...Get external data...then in files of type select ODBC databases at which point you will be faced with a series of dialog boxes inviting you to select the appropriate ODBC connection driver. You will see wizardry where you get prompted with options to connect in one of two ways
1) Windows integrated security
OR
2) SQL servers user and password
The difference between these two are basically these:
1) The computer (ie the client desktop machine) knows already who is logged on, so provided that username exists as a user on the SQL server as well with appropriate permissions to access databases and read or write to objects on the server then a password will not be required because the login name for the 'network' will be sufficient to authenticate against the server. The user has to exist as a user ALSO on the SQL server remember? so you have to create the user in SQL server itself AS WELL in order for this to work.
It makes sense doesnt it because no one else is supposed to know anyone elses password if you understand me on that point of the last paragraph.
2) A loginname and password is supplied independantly from any network login and this username and password is used to grant access in much the same way but in an 'application role' that is to say that here we are NOT relying on a network login JUST a straight command to SQL server to 'let me in because I WILL TELL YOU who I am' so to speak.
In both cases the user MUST exist as a user ALSO on the SQL server
Your question was "How do you connect and grab a password" the simple answer is YOU DON'T grab anyones password! (in short you should have NO NEED to know the password yourself if using windows integrated security) The next part of your question was "in order to process information"
If this processing of information is to... shall we say ...truncate (delete from) a table on the server then your wish would be to merely connect too authenticate your credentials against the sql server and then do the relevant process from the Microsoft Access side but from VBA.
You are not limited to only one process here you can do many many different types of data handlng processes ON THE server but from within access. Examples of this might also include creating tables permanent or temporary ,amending tables, indexing tables, throwing data around from one table to another pretty much anything you want to do as though you were WORKING actually in SQL servers itself.
I have contributed already to a similar question on this connectivity and how it can be done in another thread in which you will have the benefit of seeing ADezii's contribution and guidance on a similar method using the ADODB command object. So remember this... you are
not limited to only one way of doing things here!! both methods are very extensive. Some have a greater degree of syntax and some have less, but in each case they can to all intents and purposes be designed to do the same thing..... it depends very much on which methods you are comfortable with, where you perceive any overhead in your particular case and ultimately as always which methods you ideally prefer.
In short have a look at that thread and have a go at implementing the methods shown. If you look at the QUERYDEF method you will see the .CONNECT property which is basically YOU telling a query in code 'on the fly' how to connect to SQL server. You will see the necessary constituent parts of the
all important connection string required in order to make a successful connection.
I'm sure you will get the picture once you have a look at the thread which concerns creating a query, establishing a connection and doing a process 'all on the fly' have a go at implementing it and I wish you every success with that.
It is all advanced stuff in Access and not for the faint of heart when learning the Access program itself, but if you have difficulties get back to me.
http://www.thescripts.com/forum/thread762313.html
Hope this helps you
Regards
Jim :)
9
