Can someone help me with this Access Jet Engine Connection Error ?

[penndro]

Hi,
Please, I could use som help with a access database connection error. I have a few users who copied the database to their desktops but for some reason they are getting this runtime error -but the file works fine on my desktop:

Run-time error '-2147217865 (80040e37);
The Microsoft Jet database engine cannot find the input table or query "User_Table'. Make sure it exists and that its name is spelled correctly.

The table does exist in the database. So I am not too sure what is going on there. The code is in an Excel moduel which I am using excel to pull data from Access. The data is restricted by user type so that is why it is first looking for "User_Table".

Any help would be appreciated.


Here is my connection code:

Public Function GetWindowsName() As String
GetWindowsName = Environ("USERNAME")
End Function

Public Function Connect(pDatabase As String) As Boolean
On Error GoTo err_Connect
adoConn.Provider = "Microsoft.Jet.OLEDB.4.0"
adoConn.Properties("Jet OLEDBatabase Password") = "XXXXXXX"
adoConn.Open pDatabase
Connect = True
Exit Function
err_Connect:
Connect = False
End Function

I user the logged windows user name to validate the user in both excel and access. But I think somehow this is not connecting.

Thanks

 

[kjv1611]

Can you try it without the user validation just to make sure it is working correctly without that?

Otherwise, the first bit sounds like it may be a typo somewhere. Make sure you've got the table name 100% correct in any place you're referencing it.

 

[penndro]

Hi Kjv1611,

Thanks for trying to help me with this. I did try it as you suggested. The database brought back values in the first table but errored in all the rest. The code was erroring here (*** where i have the stars)....

Public Sub SecureAccounts()
Dim rs As New Recordset
Dim sql As String

'Get User record
sql = "SELECT * FROM User_Table WHERE UserID='" & GetWindowsName() & "'"
*** rs.Open sql, adoConn, adOpenDynamic, adLockBatchOptimistic

'Delete old secure accounts data
sql = "DELETE FROM [Secure Accounts] WHERE UserID='" & GetWindowsName() & "'"
adoConn.Execute sql
'Insert new secure accounts data
If rs!UserType = "A" Then
sql = "INSERT INTO [Secure Accounts](UserID,AccountNumber) SELECT DISTINCT '" & GetWindowsName() & "',CustomerNo FROM [Accounts Data]"
adoConn.Execute sql
Else
sql = "INSERT INTO [Secure Accounts](UserID,AccountNumber) SELECT UserID, AccountNumber FROM User_Access WHERE UserID='" & GetWindowsName() & "'"
adoConn.Execute sql
End If
End Sub

 

[vbajock]

In your function you have

Connect=true

This is incorrect, since you have not actually tested the connection state, you are simply declaring something to be true when you have nothing to really base that on. To properly test the validity of connection you would code it like this:

if adoConn.state=adStateOpen then

Connect=True

else

Connect=False

endif

See

http://www.devguru.com/Technologies/ado/quickref/connection_state.html