ADO connection problem when using Server

From: Bkraska (Bkraska_at_discussions.microsoft.com)
Date: 03/16/05 

Date: Wed, 16 Mar 2005 14:15:02 -0800

Hello,

I am using Excel 2000 VBA to connect to an MSAccess database. Everything
works great when both the Excel spread*** and the Access database are local
on my computer. When I transfer these files to our Server (Windows 2000
server) and try to run, it causes Excel to freeze up and I must restart my
computer to recover. When I look at the Access table on the server the
updated data is there, so I am assuming that the connection is OK but
possibly it cannot close the connection? Any Ideas on how to troubleshoot
this? The code I am using

Public Sub TransferDB()
Dim cnn As ADODB.Connection
Dim sPath As String
Dim sConnect As String
Dim sSQL As String
sPath = ThisWorkbook.Path
If Right$(sPath, 1) <> "\" Then sPath = sPath & "\"

Const dbfullname As String = "J:\Cost & manufacturing times\Data.mdb"
sConnect = "Provider=Microsoft.Jet.OLEDB.4.0;" & "data source=" _
    & dbfullname & ";"
    
sSQL = "INSERT INTO tblBallscrewData (Customer,Customer_PN, etc.....

Set cnn = New ADODB.Connection
cnn.ConnectionString = sConnect
cnn.Open
cnn.Execute sSQL, , adCmdText + adExecuteNoRecords
cnn.Close
Set cnn = Nothing

End Sub

Thanks for looking!

B Kraska