Home ] MS Excel Solutions ] MS Access Page ] [ SQL Server ] Tech Links ]

MS SQL Server.
Starting to do a lot more work with SQL Server - Will start to post details here soon...



Links I have found useful in the early days of using SQL Server.
http://sql-server-performance.com/articles/dba/stored_procedures_basics_p3.aspx
Choosing SQL Server 2000 Data Types
Convert Microsoft Access (JET SQL) to SQL Server (T-SQL) Cheatsheet
Data types in microsoft sql server
Home - Australian SQL Server User Group
InformIT SQL Server Reference Guide SQL Server and Microsoft Excel Integration
Microsoft Excel PivotTables 9 Great support for SQL Server Analysis Services
MSDN SQL Reference
Page 3 - Working wth Variables in Database Interactions with Transact-SQL
Page 5 - How to Receive Data from a Single Table
Populating an Access report with SQL Server Data
Some Useful TSQL Code Snippets for Beginners
SQL Server - Stored Procedures
SQL Server Excel Workbench
SQL Server Stored Procedures for Beginners
Using Data Transformation Services - Part 1
Using Data Transformation Services, part 2
Using Data Transformation Services, part 3
Using Data Transformation Services, part 4
Write query results to Excel using a linked server and T-SQL
http://rodeworks.com/wp-content/uploads/2008/08/msaccess_and_sqlserver.pdf

 


What would be a good website to start learning SQL?

This covers the basics very well:
http://www.vb6.us/tutorials/introduction-sql-structured-query-language


How do I find out my Connection String?

Please follow the steps below:
1.) Create a text file myConnect.txt
2.) Rename the text file to myConnect.udl
3.) Double click on the new file
4.) Goto Connection Tab
5.) Click on Use Connection String
6.) Click on Build
7.) Goto Machine Data Source
8.) Click New..
9.) Select System Data Source - Next >
10.) Select SQL Server
11.) Next and Finish
12.) Under 'Create a New Data Source to SQL Server' window - Enter a name
for the DSN, description and select a server from the dropdown list
13.) Depending on your system setup choose either NT authentication or SQL
Server Authentication (check with your DBA)
14.) Click Next
15.) Check Change the default database to: <Your database name> ie
Northwind
16.) Click Finish - > Test Data Source... -> OK
17.) Ok
18.) OK
19.) OK
20.) Once all the windows are closed, open the udl file with a notepad

Your connection string to the server will display as follow:

[oledb]
; Everything after this line is an OLE DB initstring
Provider=MSDASQL.1;Persist Security Info=False;Extended
Properties="DSN=test;APP=Microsoft® Windows® Operating
System;WSID=WKS001;DATABASE=Northwind;Trusted_Connection=Yes"

Copy this connection string and embed it within your VBA code for an ADO
recordset to use.

Thanks to Vincent Tan - Excel-L Mailing List


Optimizing Microsoft Office Access Applications Linked to SQL Server

Lots of advice and tips and code here from Andy Byron:

Optimizing Microsoft Office Access Applications Linked to SQL Server
http://msdn.microsoft.com/en-us/library/bb188204(SQL.90).aspx

He even discusses some of the unbound and locking issues:
http://msdn.microsoft.com/en-us/library/bb188204(SQL.90).aspx#optaccsql_topic5


Thanks to Gustav Brock - Access D Mailing List