[AccessD] Accessing a SQL DB

Here is Carl Prothman's site that recently moved.
It has samples of many database OLEDB and .Net Providers , ODBC Driver 
Connection strings
along with a series of ADO and ADOX FAQ's

http://www.carlprothman.net/

One other trick I use is to check the connection is open right after opening

Dim cnn As ADODB.Connection
Set cnn = New ADODB.Connection
with .cnn
....... etc
 .Open
End With
'Make sure the connection opened:
If Not CBool(cnn.State AND adStateOpen) Then
  'Could not open the connection:
  MsgBox "Could not open connection..."
Else
  'Process data as usual here...
End If

The Connection object supports several descriptive values: 
adStateClosed, adStateOpen, adStateConnecting, adStateExecuting, and 
adStateFetching. Each constant represents a number, but you really don't 
care about the numeric value. What's important is that two expressions, 
evaluated with the AND operator, return True if the values are the same 
and False if they're different.


Jim Lawrence wrote:

>Hi John:
>
>Thanks you for all your information. I will test it out right away.
>
>Jim 
>
>-----Original Message-----
>From: accessd-bounces at databaseadvisors.com
>[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of John Ruff
>Sent: Friday, November 11, 2005 5:31 PM
>To: 'Access Developers discussion and problem solving'
>Subject: Re: [AccessD] Accessing a SQL DB
>
>connectionString = "Provider=SQLOLEDB.1;
>				Integrated Security=SSPI;
>				Persist Security Info=False;
>				Initial Catalog=CommonTables;
>				Data Source=MONSTER
>
>This connects to a SQL Server 2000 called Monster and the table CommonTables
>
>By the way, there is a relatively easy way of capturing a connection string
>for any SQL Server DB.
>
>1. Create a blank text file (FindSQL.txt for example).
>2. Change its extension to .udl (FindSQL.udl)
>3. Open the .udl file and follow the Wizard to connect to the SQL Server of
>your choice.
>4. After completing the Wizard, close the file and change its extension back
>to .txt (FindSQL.txt)
>5. Open the FindSQL.txt file again and you will find that the 2nd line has
>your connection string all ready for you to cut and paste into your code.
>
>Enjoy,
>
>
>John V. Ruff - The Eternal Optimist :-)
> 
>"Commit to the Lord whatever you do,
>                and your plans will succeed." Proverbs 16:3
> 
>-----Original Message-----
>From: accessd-bounces at databaseadvisors.com
>[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Jim Lawrence
>Sent: Friday, November 11, 2005 5:17 PM
>To: 'Access Developers discussion and problem solving'
>Subject: [AccessD] Accessing a SQL DB
>
>OT:
>
>Hi All:
>
>This is a little off topic but has anyone had experience connecting to a
>full MS SQL DB from a beta version of Visual Studio 2003?
>
>I am currently working with an ASP project and just can not get the program
>to recognize the MS SQL server. It is local but I just can not get the
>correct connection string sequence.
>
>This is a connection string used to connect to an MS SQL Express DB:
>connectionString="Data Source=.\SQLExpress;Integrated 
>			Security=True;
>			UserInstance=True;
>			MyDBFilename=|DataDirectory|mydbfilename.mdf"
>providerName="System.Data.SqlClient"
>
>The above connects fine. I can even view the 'Express' server in the 'Server
>Explorer' but the scan ignores the MS SQL 2000. It is definitely there as
>other applications have no difficulty see and connecting to it.
>
>Below is one of the many connection string combinations that I have tried.
>
>connectionString="Provider=sqloledb;
>			Data Source=xpserver;
>			Initial Catalog=pubs;
>			Integrated Security=SSPI;"
>
>Or
>
>The above was modeled from proven strings and from samples at
>http://www.connectionstrings.com/
>
>
>Any comments of assistance would be greatly appreciated.
>Jim
>
>  
>

-- 
Marty Connelly
Victoria, B.C.
Canada