ADO connection problem when using server

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!

--
B Kraska

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


"Bkraska" wrote:

> 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!
>
> --
> B Kraska

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

"Gary Brown" wrote:

> 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
>
>
> "Bkraska" wrote:
>
> > 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!
> >
> > --
> > B Kraska

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

"Gary Brown" wrote:

> 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
>
>
> "Bkraska" wrote:
>
> > 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!
> >
> > --
> > B Kraska

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


"Jim Thomlinson" wrote:

> 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
>
> "Gary Brown" wrote:
>
> > 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
> >
> >
> > "Bkraska" wrote:
> >
> > > 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!
> > >
> > > --
> > > B Kraska

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

HTH

"Bkraska" wrote:

> I am getting a Compile error: "Constant expression required" in the Const
> dbfullname line?
>
> "Gary Brown" wrote:
>
> > 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
> >
> >
> > "Bkraska" wrote:
> >
> > > 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!
> > >
> > > --
> > > B Kraska

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


"Jim Thomlinson" wrote:

> You can not assign to a constant wiht a function (I think). Change that to a
> variable.
>
> HTH
>
> "Bkraska" wrote:
>
> > I am getting a Compile error: "Constant expression required" in the Const
> > dbfullname line?
> >
> > "Gary Brown" wrote:
> >
> > > 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
> > >
> > >
> > > "Bkraska" wrote:
> > >
> > > > 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!
> > > >
> > > > --
> > > > B Kraska

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.

"Bkraska" wrote:

> 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!
>
> --
> B Kraska