我有一個使用 SQL 資料庫作為后端的 MS Access 前端。要進行連接，我使用 ODBC 連接并在“ODBC 資料源（32 位）”中創建了所需的條目
當我將資料庫提供給其他人時，他們將需要創建此資料源。我有一個批處理檔案，所以他們可以運行它。
如果他們不運行它，他們將收到類似“ODBC 連接到 XY 失敗”的錯誤。我怎么能改變這個錯誤，或者至少在之后寫第二個訊息框，我可以告訴他們“運行批處理檔案 XY 來連接”？
uj5u.com熱心網友回復：
當我將資料庫提供給其他人時，他們將需要創建此資料源。
這是你的第一個錯誤和假設。您當然不想以這種方式部署您的應用程式。
你的部署方式？
您獲取 accDB 檔案，并將表鏈接到 sql server。您使用所謂的無 DSN 連接進行鏈接。這種連接不需要在每個作業站上設定任何資料源。
所以，好的，現在您將表鏈接到 sql server（生產版本 - 您可能正在開發人員 PC 上進行本地開發，并使用 sql server express edition 的本地副本。
因此，現在您將表鏈接到他們的服務器，然后您現在將 accDB 編譯為已編譯的 Access 可執行版本 - accDE。
您現在可以自由地將這個“應用程式”部署到該公司的任何和所有作業站 - 他們不必重新鏈接表，不必設定資料源，事實上他們不必做任何事情，但只需運行/啟動應用程式。
如何建立并獲得無 dns 連接？
好吧，最簡單的方法是總是，但總是總是使用 FILE dsn 創建鏈接表。事實上，當您從 Access 啟動 ODBC 連接管理器時，默認是使用 FILE dsn。永遠不要，也不要使用“系統”或“用戶”dsn。
如果您使用該 FILE dsn 鏈接表，則 Access 會將它們轉換為無 DNS 連接。那時，您甚至可以洗掉您創建的 DSN - 訪問 100% 會忽略 DSN，您不再需要它。
接下來：
如果您使用的是 nc 17 或 nc 11-18 驅動程式？那么是的，每個作業站都必須安裝相同的驅動程式。或者，您可以使用較舊的“舊版”sql 驅動程式。但要小心 - 舊版驅動程式不支持 datetime2 列資料型別。
所以，MAKE double、triple、quadrible 確保您不使用需要較新驅動程式的資料型別和列。
現在，對于一些較大的站點，我們仍然使用較舊的“舊版”驅動程式，因為該驅動程式是在作業系統級別安裝的，并且已經安裝在回到 windows xp 的每個 windows 副本上 - 實際上是 windows 98SE 版開始運送該驅動程式。因此，您可以 100% 假設舊版 sql 驅動程式已經并將安裝在每個作業站上。
并且通過使用 采用 dns-less 連接，則不需要在每個作業站上設定連接。當您鏈接表時，只要這些作業站與 sql server 位于同一網路上，那么它們就可以使用。
現在，在一些網站上，我們實際上甚至不能在現場，我們甚至不能預鏈接到他們的 sql server。因此，我們所做的是在應用程式啟動時，我們檢查鏈接表的當前鏈接，如果它與我們在設定作業站時也包含的一些外部文本檔案不匹配，那么我們使用 VBA 代碼重新鏈接啟動時的表。但是再一次，在 VBA 中重新鏈接表格很容易，并且再次不需要在每個作業站上進行任何型別的“dsn”或 odbc 設定。
And in fact, another way we used to do this is have a table in the front end, and it had one record, and that record had the connection string. So, right before deployment, we just edit that one record table in the front end to have the correct connection to their sql server. And once again, on startup , we check a linked table, and see if the connection strings match, if they don't, then we run the VBA re-link code, and once again, zero configuration and zero need exists to setup anything at all on each work station.
So, as a general rule, every dog, frog, insect that deployed access applications? We setup some re-link code and check that link on startup. In fact most developers have done this even without sql server - and even when using a access back end, then re-link code is included to resolve this issue.
but, be it linking to a access back end, or sql server back end? Some time of link check and system is assumed to have been cooked up by you, and this code will run on startup to check if the linked tables are pointing to the correct location.
But, be the back end oracle, sql server or whatever? You can create what is called dsn-less linked tables. As noted, you can use VBA to do this, or in fact you can use the linked table manager ---- as LONG AS you use a FILE dsn when you linked, then access coverts that to dns-less for you, and you be good to go.
so, in effect, you don't have to test/check/know for a odbc fail, since you checked the correct connection string on startup.
However, there is a way to trap, and check for a odbc failure, and this involves using a DIFFERENT way to connect, since we all know that if you have a odbc fail, you are duck soup (you have to exit Access, and there is NO KNOWN way around this issue - (well, except for testing if you can connect, and you do NOT use a linked table - since as noted once a odbc connect error triggers, it is game over.
The way you do this "alternate" test is like this:
Function TestLogin(strCon As String) As Boolean
On Error GoTo TestError
Dim dbs As DAO.Database
Dim qdf As DAO.QueryDef
Set dbs = CurrentDb()
Set qdf = dbs.CreateQueryDef("")
qdf.connect = strCon
qdf.ReturnsRecords = False
'Any VALID SQL statement that runs on server will work below.
' this does assume user has enough rights to query built in
' system tables
qdf.sql = "SELECT 1 "
qdf.Execute
TestLogin = True
Exit Function
TestError:
TestLogin = False
Exit Function
End Function
So, above does not hit or use a linked table. And a HUGE bonus of above? Once you execute that above valid logon, then any and all linked tables will work - AND WILL WORK without even having included the user/password in that connection string for the linked table. This is in have a huge bonus in terms of security, since now you don't have to include the user/password in the linked tables which of course exposes the user/password in plane text for all users that could look and see and find the sql user/password used.
In fact, what this means is that you can link your tables, but NOT include the user/password when you link the tables!!! - this is a HUGE security hole plugged and fixed when you do this.
So, once a valid logon has occurred (such as above), then any and all linked tables will now work, and work without even having included the user/passwords in those linked table connection strings.
As noted, the other big bonus is that you can use the above code to test for a valid connection, and avoid that dreaded "odbc" error, since as noted, if a odbc connection error is EVER triggered at any point in time, you MUST exit the applcation - no other way out.
However, it should be noted, that if you ever are going to use a wi-fi connection, or say cloud based sql server say running on Azure?
In that case, often with wi-fi, or a cloud based edition of sql server, then of course such connections over the internet are prone to minor and frequent dis-connects.
ODBC was developed long before the internet, and long before people would do things like connect access to some cloud based sql server running, and using a connection over the internet. But, if this turns out to be your use case, and deployment case?
Then you have to bite the bullet and ASSUME and ENSURE that you now adopt the nc 11-18 drivers. (I would go with nc17). These new drivers are now "internet" aware, and they are able to gracefully handle minor dis-conects, and in fact automatic re-cover and re-connect.
So, if you are ever going to use wi-fi, or connect to cloud based server? Then yes, you have to link the tables using say nc17 newer drivers, and you ALSO MUST THEN ensure that the same driver version you linked tables with is to be installed on each work station. You still don't have to setup any dsn connection and all that jazz - but you do have to ensure that the driver you used is ALSO installed on those work stations.
As noted, for larger deployments, we thus use the standard "legacy" sql driver, as it would be too painful to go to all work stations and install this driver.
However, we had one location, and for months they were experience in odbc connection failure. We had them replace a router, and even a network card on a server - but the problem still remained.
We suspect that some workstations had aggressive power management turned on, newer windows 10-11 will often put the network card to sleep, and thus when using access, we were seeing odbc errors. So, for that company, we had them install nc17, and linked access to sql server using that driver, and the problem went away (because those newer drivers now have built in re-connect ability - this is a relative new feature of ODBC, and one that legacy systems and drivers don't have.
轉載請註明出處，本文鏈接：https://www.uj5u.com/houduan/451418.html
標籤：毫秒访问