Re: ADO connection problem when using Server

From: Val Mazur \(MVP\) (group51a_at_hotmail.com)
Date: 03/18/05 

Date: Thu, 17 Mar 2005 21:00:39 -0500

Hi,

I do not see anything strange in a code that could freeze the application. I
believe you could debug VBA code in Excel placing breakpoint in a code and
when you run it, then debugger should stop on it and you could execute the
code line-by-line to see where it hangs. 

-- 
Val Mazur
Microsoft MVP
http://xport.mvps.org
"Bkraska" <Bkraska@discussions.microsoft.com> wrote in message 
news:6B3D52F0-E671-4F96-9AF9-51D316790ABC@microsoft.com...
> 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