March 2, 2009 at 12:09 pm
check this out, that'll give you a good idea of what is possible using analysis services in excel :
March 19, 2009 at 6:08 am
ChrisStar (2/26/2009)
To start off, I am very new to SQL Server and have been using Access for many years but have limited coding experience. What I have learned/discovered I have done by trial and error and by googling and reading forums such as this. I now have an issue that I hope someone can shed light on for me. I have migrated my old Access database to utilize the Access front-end (2003) and a SQL Server 2008 back-end and have converted multiple queries to pass-through queries to utilize the server. Is there any way to have these queries reference a single entry for the odbc string rather than entering it into the properties of each query? I'm trying to eliminate maintenance time/issues if I have to update the string later. Thank you for any and all help.Chris
Here is some (untested) VBA code to show how to loop through the queries and update all the connection strings in an MDB. This requires a reference to the DAO library.
Sub SQL_RelinkQueries()
Dim db As DAO.Database, qdf As DAO.QueryDef
Dim s As String
s = "your ODBC connection string goes here"
Set db = CurrentDb
For Each qdf In db.QueryDefs
If qdf.Connect > "" Then
qdf.Connect = s
End If
Next qdf
Set db = Nothing
End Sub
March 20, 2009 at 5:59 am
If your queries do not rely too heavily on functions that are available in Access but not in T-SQL, you should consider porting those queries to the server, as stored procedures or as views.
That way you would be able to execute the stored procedure towards a single connection defined in Access:
Dim Cnn1 As ADODB.Connection
Dim MyRs As ADODB.Recordset
Dim MyCommand As ADODB.Command
Const CURRENT_SERVER = "driver={SQL Server};server= ...
Set Cnn1 = New ADODB.Connection
Cnn1.Open CURRENT_SERVER
Set MyCommand = New ADODB.Command
With MyCommand
.ActiveConnection = Cnn1
.CommandText = "SELECT * FROM Authors"
Set MyRs = MyCommand.Execute
End With
MyRs.Close
Set MyRs = Nothing
Set MyRs = New ADODB.Recordset
MyRs.Open MyCommand, , adOpenKeyset, adLockOptimistic, adCmdText
If you work with views (and views can be prepared by a stored procedure on the server), you can create a linked table in Access on the fly (here using DAO):
Function AttachServerTables()
Dim tdf As DAO.TableDef
Dim dbs As DAO.Database
Dim strCnn As String
dim strTableName as String
Const CURRENT_SERVER = "driver={SQL Server};server= ...
strCnn = "odbc;" & CURRENT_SERVER
' Set dbs = Currentdb usually works also.
Set dbs = DBEngine.Workspaces(0).Databases(0)
' strTableName is something like dbo_xxxxx
strTableName = "Name of the table or view I want to open"
' just in case it already exists
DoCmd.DeleteObject acTable, strTableName
Set tdf = dbs.CreateTableDef()
tdf.Name = strTableName
' remove 'dbo_' for the table on the server
tdf.SourceTableName = Mid(!TableName, 5)
tdf.Connect = strCnn
dbs.TableDefs.Append tdf
dbs.Close
Set dbs = Nothing
End Function
After processing the data you can delete the link to the table or view with:
DoCmd.DeleteObject acTable, "TableName"
That way, no permanent object in Access remains linked to the server (useful when you frequently move your application from one server to another and for security reasons).
For the various connection strings that are available, see:
http://www.carlprothman.net/Default.aspx?tabid=81
Have a nice day!
April 2, 2009 at 1:59 pm
William Mitchell and rff,
Thank you both for the great responses. I will be working on this project again in the near future (it got tabled for others, imagine that!). I'll try them both out.
Thanks again!!
Chris
April 3, 2009 at 7:44 am
UPDATE:
I was able to work on my database and ended up with this. I just added a little bit so that I get a prompt to enter the new ODBC string in. Works like a charm!!!!:-):-) Thank you so much to all for their help and a very special "Thank You" to William Mitchell for leading me in the right direction. Your VBA code is now tested and works!! I not only solved my problem (and saved me lots of time!) but I have learned at least a little about the DAO library.
Below is what I ended up with that worked for me!
Have a great day!! Chris
Sub Update_Qry_ODBC_btn_Click()
Dim strPrompt As String
strPrompt = "Enter new ODBC connection information:"
FilePath = InputBox(strPrompt, AppTitle)
Dim db As DAO.Database, qdf As DAO.QueryDef
Dim S As String
S = FilePath
Set db = CurrentDb
For Each qdf In db.QueryDefs
If qdf.Connect > "" Then
qdf.Connect = S
End If
Next qdf
Set db = Nothing
End Sub
Viewing 5 posts - 16 through 19 (of 19 total)
You must be logged in to reply to this topic. Login to reply