ADO connection problem when using server

  • Thread starter Thread starter Guest
  • Start date Start date
G

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!
 
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"
Click to expand...

'/==========================================/
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
 
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
 
I am getting a Compile error: "Constant expression required" in the Const
dbfullname line?
 
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
 
You can not assign to a constant wiht a function (I think). Change that to a
variable.

HTH
 
Thanks Jim,
I still have the problem with Excel locking up however. The drive on the
server is compressed. Would that make a difference?
 
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.
 
Back
Top