ADO connection problem when using server

[Guest]

I am using the following code to connect to an Access Database. Everything
works great when both the Excel workbook and Access database are on my local
C: drive. When I try to put both on the network drive (Windows 2000 server)
and run the spreadsheet from there Excel will freeze ( I do not get any error
message and must restart my computer) If I check the Access table, the
updated data is there so I am assuming that the connection is OK but for some
reason maybe it cannot close the connection? Does anyone have any Idea what
could cause this, and how I might troubleshoot this?

code:

Public Sub TransferDB()
Dim cnn As ADODB.Connection
Dim sPath As String
Dim sConnect As String
Dim sSQL As String

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........

VALUES ( '" _
& TxtCustomer & "', '" 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 taking the time to read this Post!

 

[Guest]

Is it possible that the local name 'J' is confusing the issue? To change the
local drive name to a UNC, use the following function...

Const dbfullname As String = UNCfromLocalDriveName("J") & "\Cost & manufacturing times\Data.mdb"

'/==========================================/
Function UNCfromLocalDriveName(strLocalDrive) As String
'Find UNC from Local path
'i.e. Local drive "F:" = "\\RdaServer3\sys1"
' example of usage: UNCfromLocalDriveName("P") <-Actual Drive Letter
' or UNCfromLocalDriveName(A2) <-Cell reference
'
Dim sLocal As String
Dim sRemote As String * 255
Dim lLen As Long

Application.Volatile

sRemote = String$(255, Chr$(32))

lLen = 255
sLocal = strLocalDrive & ":"

WNetGetConnection sLocal, sRemote, lLen

UNCfromLocalDriveName = Trim(sRemote)

End Function
'/==========================================
HTH,
Gary Brown

 

[Guest]

I tried your code and it would not compile. You did not mention the API
declaration. Once I looked it up the function works great... Here is the
declaration I found... Thanks for the cool code

Declare Function WNetGetConnection Lib "mpr.dll" Alias "WNetGetConnectionA"
( _
ByVal lpszLocalName As String, _
ByVal lpszRemoteName As String, _
ByRef cbRemoteName As Long) As Long

 

[Guest]

I am getting a Compile error: "Constant expression required" in the Const
dbfullname line?

 

[Guest]

I am REALLY sorry!!!!
That teaches me for pulling a snippet of code looking at the whole code.
Again, sorry for the extra work.
Sincerely,
Gary Brown

 

[Guest]

You can not assign to a constant wiht a function (I think). Change that to a
variable.

HTH

 

[Guest]

Thanks Jim,
I still have the problem with Excel locking up however. The drive on the
server is compressed. Would that make a difference?

 

[Guest]

OK some progress. The original spreadsheet was sending 60 fields to the
Access database. I reduced this to 6 fields and it worked, but very slow. It
took maybe a full minute. Back to the original, I let it sit over an hour
and a half and still would not finish. Is there a more efficient way to
transfer data from Excel to Access? I had heard that ADO is slower than DAO
but this is ridiculous. I am really wondering now if it is due to the
compressed drive. Where can I find more information on this? And again
thanks to all who have read and posted, or just read this post.