Strange problem with Access ADODB connection to SQL Server (1 Viewer)

Derevon

Registered User.
Local time
Today, 14:24
Joined
Jan 14, 2014
Messages
51
Hi everyone,

An Access database that we have developed doesn't work properly on a few computers in the company which use Windows 7, and we haven't been able to find any difference between the installed related software on those computers with Windows 7 where it works and those where it doesn't. All computers have the same release version of Office (1806), etc.

The error we get is a a run time error: -2147467259, as soon as the code that is trying to connected to the SQL Server database is launched.

The problem doesn't seem to appear in every release we make, though, but in most. Sometimes just doing some random changes in the database like compacting it or temporarily changing a line of code and changing it back exactly as it was can make a broken version work, and vice versa.

Today I attempted to take a working version of the database and compile it to ACCDE on one of the computers with problems, and then it worked on these few computers with the problem, but then instead it didn't work on any other users' computers (regardless of Windows 7 or Windows 10).

The code causing the error is the rs.Open line below (the SQL code itself doesn't seem to have any bearing on the error):

Code:
Dim rs As Object
Set rs = New ADODB.Recordset
rs.Open "[SQL CODE]", getCS() & Decrypt(getPW()), adOpenForwardOnly

The connection string and SQL server password are passed by functions as can be seen. The connection string looks like this:

Code:
"Provider=SQLOLEDB;SERVER=[SERVERNAME];DATABASE=[DBNAME];UID=[ID];PWD="

Once we were trying to put the connection string and password directly without functions, and we had the same error. Then we tried changing back to the function, and it suddenly worked. Another line which had the code directly, though, wouldn't work.

This all looks to me like some sort of bug somewhere, but we have no idea where if so.

Does anyone have any idea what could be causing this problem and/or a way to circumvent it (short of attempting to reinstall Windows 7 or Office or upgrading to Windows 10)?

Thank you
 

ByteMyzer

AWF VIP
Local time
Today, 06:24
Joined
May 3, 2004
Messages
1,409
For the Windows 7 computers exhibiting problems, are they, by any chance, pre-Service Pack 1?
 

Derevon

Registered User.
Local time
Today, 14:24
Joined
Jan 14, 2014
Messages
51
They are both SP1. Today there was even a Windows 10 computer which had this problem.

One interesting thing, it seems that if we open one of these "broken" files, and go to the VBA References, and unclick or click on some random libraries, it seems to work after that.
 

Derevon

Registered User.
Local time
Today, 14:24
Joined
Jan 14, 2014
Messages
51
So it seems the problem has nothing to do with Windows 7 vs Windows 10.

We have been able to narrow the problem down to VBA compilation. The problem occurs when the VBA is compiled. To fix it, one can go to the references and remove a library and readd it and it will work again.

Anyone has any idea about possible causes for this error or ideas for possible workarounds?

Thanks
 

ByteMyzer

AWF VIP
Local time
Today, 06:24
Joined
May 3, 2004
Messages
1,409
To identify possible causes, we would first have to know which library(ies) you had to remove from the references. Care to share more details?
 

NauticalGent

Ignore List Poster Boy
Local time
Today, 09:24
Joined
Apr 27, 2015
Messages
6,286
No idea. From what you posted, it matters not what ref you remove and re-add? Strange...

Only thing I would suggest would be a decompile...
 

Derevon

Registered User.
Local time
Today, 14:24
Joined
Jan 14, 2014
Messages
51
Right, it doesn't matter what reference is removed or added as long as you make a change. Maybe just because it makes Access go from compiled to non-compiled.

Other things that we have tried is changing the bindings from early to late, but no difference.

We also tried creating a brand new database and making a new module which just the bare minimum lines of code and same issue.

However, now we also tried using the command object instead of the recordset object, and it doesn't have the same problem it seems. The below code seems to work fine compiled:

Code:
Public Sub Test2()
    Dim cmd As Object
    Dim rs As Object
    Set cmd = CreateObject("ADODB.Command")
    cmd.ActiveConnection = "Provider=SQLOLEDB;SERVER=########;DATABASE=########;UID=########;PWD=########"
    cmd.CommandType = 1
    cmd.CommandText = "SELECT * FROM [TABLE]"
    Set rs = cmd.Execute
    Set cmd = Nothing
End Sub

If we can't find another way, perhaps we could try converting all functions to use ADODB.Command instead of ADODB.Recordset.
 

Derevon

Registered User.
Local time
Today, 14:24
Joined
Jan 14, 2014
Messages
51
As it turns out, the problem was caused by the decryption function. On certain computers, while compiled, the function generated one of the password characters incorrectly for some strange reason.

We replaced the function with another, and now it seems to work fine. :)
 

Users who are viewing this thread

Top Bottom