I've agreed with PKStormy that questions with potential public interest will continue to be dealt with in the forum thread
So taking points from PK's last 2 posts together ...this is a very long answer!
What made you explore using MS Access and SQL Server over something like .NET?
I thought about learning .NET about 10 years ago but heard there a steep learning curve.
As I had no real need for it, it was a good excuse to not do so
Did you by chance get the chance to try the MS Access 'Star' feature prior to SQL Server days?
I didn't start using Access until v97 and only became a serious user around 2000
TBH that's the first I've heard of this 'Star' feature!
I wonder what the code to import the SQL Server tables would look like using a DSN-Less connection.
....
I'm hoping to modify the code to import SQL Server stored procedures instead of SQL Server tables to overcome big data size issues on tables that would cause the MS Access file to exceed it's 2 gig filesize limitations.
Somewhat confused by these two comments.
My SQL tables are LINKED to ACCESS FEs.
Why would you IMPORT SQL tables rather than LINK them?
Linking means there is no reason why the Access FE would ever reach the 2GB size limit
I use this code in the example which is based on an ODBC Dsn.
Dim cxnString As String
cxnString = "ODBC;DSN=" & ODBCName & ";UID=" & UIDLogin & ";" & UIDPW & ";LANGUAGE=us_english;DATABASE=" & SQLDBName
DoCmd.TransferDatabase acImport, "ODBC Database", cxnString, acTable, SQLTableName, SQLTableName
If possible, could you translate the above to use a DSN-Less connection? It's been a while since I've worked with DSN-Less connections. I'm going to do some research on using them again.
Sure. There are lots of similarities except the equivalent would be acLink not acImport
The link details are held in 2 tables - tblTableLinkTypes & tblTableLinks
The relink form below shows the details for one client school - each school will have at least 2 of the link types (starting with SDA) but if they have purchased one or more additional apps (CEO/SPS) could have those as well.
The connection strings will of course differ for each client school depending on file location (Access) / SQL server details
The linked table form lists all the tables to be linked from each external app - in this case a total of 305 linked tables
Before distributing a new or updated version to clients via my website, the table links are deliberately broken.
When the client sysadmin installs the new FE, opens direct to the relink form.
The app pulls information stored in the Access backup or config file to detect the correct table links to be used
The relink process does a number of actions including
1. Relink all required tables which takes 30 seconds or so for this very large app
2. Makes any required changes to the structure or data in linked SQL & Access tables
3. Resets all options as used by that particular client
4. Updates the version information in the registry
5. Updates values in the Access config file
6. Resets folder & file paths from client settings for all linked images/docs/videos used in the bespoke app help system
7. Various other housekeeping tasks
8. Populates a version update log file etc ....
The 'heavy lifting' for the relink process is done by the GetConnectionString function which covers a wide range of possible connection types - both SQL & Access
Code:
'############################################
' GetConnectionString 04/08/2010
' Used to create connection strings for each of the link types/tables in use
'############################################
Function GetConnectionString(strLinkType, _
strLinkServer, _
strLinkDatabase, _
strLinkUsername, _
strLinkPassword) As String
Select Case strLinkType 'Get the link type to determine the connection string
#If SQL_DRIVER = "Native" Then
Case "SQL"
GetConnectionString = "ODBC;DRIVER={SQL Native Client};" & _
"SERVER=" & Nz(strLinkServer, "") & ";" & _
"DATABASE=" & Nz(strLinkDatabase, "") & ";" & _
"APP=SchoolDataAnalyser;" & _
"UID=" & Nz(strLinkUsername, "") & ";" & _
"PWD=" & Nz(strLinkPassword, "")
Case "SQL-Trusted"
GetConnectionString = "ODBC;DRIVER={SQL Native Client};" & _
"SERVER=" & Nz(strLinkServer, "") & ";" & _
"DATABASE=" & Nz(strLinkDatabase, "") & ";" & _
"APP=SchoolDataAnalyser;" & _
"Trusted_Connection=yes;"
#ElseIf SQL_DRIVER = "MDAC" Then
Case "SQL"
GetConnectionString = "ODBC;DRIVER={SQL Server};" & _
"SERVER=" & Nz(strLinkServer, "") & ";" & _
"DATABASE=" & Nz(strLinkDatabase, "") & ";" & _
"APP=SchoolDataAnalyser;" & _
"UID=" & Nz(strLinkUsername, "") & ";" & _
"PWD=" & Nz(strLinkPassword, "")
Case "SQL-Trusted"
GetConnectionString = "ODBC;DRIVER={SQL Server};" & _
"SERVER=" & Nz(strLinkServer, "") & ";" & _
"DATABASE=" & Nz(strLinkDatabase, "") & ";" & _
"APP=SchoolDataAnalyser;" & _
"Trusted_Connection=yes;" & _
""
#End If
Case "Access"
If Nz(strLinkPassword, "") <> "" Then
GetConnectionString = "MS Access;" & _
"PWD=" & Nz(strLinkPassword, "") & ";" & _
"DATABASE=" & Nz(strLinkServer, "") & Nz(strLinkDatabase, "")
Else
GetConnectionString = "MS Access;" & _
"DATABASE=" & Nz(strLinkServer, "") & Nz(strLinkDatabase, "")
End If
Case Else
GetConnectionString = ""
End Select
End Function
The string values are of course taken from the link types table - see form in first screenshot
This is part of the relink code which loops through each of the 305 tables in turn, shows progress & adds info to the log file
Code:
Function RelinkAllTables(boStatusBar As Boolean, boStatusMessage As Boolean) As Boolean
...
strText2 = "Getting data for new connections"
...
Set MySet = db.OpenRecordset("qryTableLinkInfo", dbOpenSnapshot)
MySet.MoveLast
MySet.MoveFirst
Dim intCountOflinks As Integer
Dim intCurrentLink As Integer
intCountOflinks = MySet.RecordCount
icount = MySet.RecordCount
intCurrentLink = 0
SetupProgressBar 'reset to zero
DoEvents
Do Until MySet.EOF
intCurrentLink = intCurrentLink + 1
strText2 = "Relinking table " & intCurrentLink & " of " & intCountOflinks & " - " & MySet!TableAlias
'Debug.Print strText2
If boStatusBar Then
StatusBar strText2
frm.LblHelpText.Caption = strText2 & " . . ."
UpdateProgressBar
DoEvents
End If
If MySet!LinkActive = True Then
strConnectionString = ""
strConnectionString = GetConnectionString(MySet!LinkType, MySet!LinkServer, MySet!LinkDatabase, MySet!LinkUsername, MySet!LinkPassword)
' Debug.Print strConnectionString
If strConnectionString = "" Then
strStatusMessage = strStatusMessage & MySet!TableName & " has a bad linktype, "
boMakeLink = False
RelinkAllTables = False
End If
If LinkTable(MySet!TableName, MySet!TableAlias, strConnectionString, True) = False Then 'Link the table
strStatusMessage = strStatusMessage & MySet!TableName & ", "
boMakeLink = False
RelinkAllTables = False
End If
End If
DoEvents
MySet.MoveNext
Loop
....
End Function
HTH
Any questions?