Solved

VB6 & Access 200 DB - works great on most Win2k boxes, but not on 2 ----- HELP!!

Posted on 2004-03-26
14
272 Views
Last Modified: 2012-05-04
This is a second post from the VB DB topic area - but due to lack of response I am posting it here as well.


Ok, i have this VB6 app that talks to an Access 2000 DB.  The users login to the app, then a basic set of data is pulled from the DB based on their login.  On my PC, I can login, and see the basic data.  On the problem PCs (there are 2 of them) I can login, but the basic data is not pulled from the DB.  Now, I know they are talking to the DB correctly since it logs them in and asks them a question based on their login.  Also, the DB is not corrupt as I have taken the working DB on my PC and trandfered it to their PCs and still no joy.  

Here is a desc. of my machine and the problem PCs:

WORKING           - Win2k SP4 - MDAC 2.8 from 2.6, Access 2000 (v 9.0.2720) -- this is my PC
                         - Win XP SP1 - MDAC 2.7 SP1, Access 2002 (v. ??) --- another users PC

NOT WORKING (others) -  (1) Win2k SP4 (French Version) - MDAC 2.8 from 2.6, Access 2000 (v. 9.0.2812)
                                  -   (1) Win2k SP4 (French Version) - MDAC 2.8 from 2.5, Access 2000 (v9.xxx SR-1) - I don't remember the exact version number except that it was SR-1

I should mention that these are traveling users so I do not have access to the PCs.  I will be getting them Friday evening - gotta love working late on Fridays... ;-)

Am I leaving any info out that you need?  Any help would be appreciated!  TIA for the help!

-Craig


**Note:  It was suggested that I deploy my app via the Package and Installation wizard - but I am already doing it that way.  
0
Comment
Question by:cas27
14 Comments
 
LVL 28

Expert Comment

by:vinnyd79
ID: 10687924
Did you set the cursorlocation on your connection to adUseClient?
0
 
LVL 48

Expert Comment

by:Mikal613
ID: 10688165
Post the code
0
 

Author Comment

by:cas27
ID: 10688354
Here is a code snippet from my main module


Public DBConnection As New ADODB.Connection
------------------------------------------------------
AccessConnect = "Driver={Microsoft Access Driver (*.mdb)};" & _
                  "Dbq=func_db.mdb;" & _
                  "DefaultDir=" & App.path & ";" & _
                  "Uid=Admin;Pwd=;"
DBConnection.Open AccessConnect

------------------------------

Then in a seperate from:

Set FunctionRS = DBConnection.Execute( _
    "Select min(function_id)AS MIN_ID from function where project_id= " & appProjectID)

Apparently something goes wrong with this statment, but I haven't had the time to debug this extensively.  I will be headed into a 4-5 hour meeting in just a bit, so don't be surprised if I don't respond before 1pm PST.

Thanks!

Craig
0
 
LVL 2

Expert Comment

by:WintersVine
ID: 10689162
it needs to read: (note the ' on either side of your variable appProjectID)

Set FunctionRS = DBConnection.Execute( _
    "Select min(function_id)AS MIN_ID from function where project_id= '" & appProjectID & "'")
0
 
LVL 18

Expert Comment

by:Sethi
ID: 10692840
The As clause in this statement needs a space in between:

Set FunctionRS = DBConnection.Execute( _
    "Select min(function_id) AS MIN_ID from function where project_id= " & appProjectID & "")

There is no need for single quotes if project_id is numeric.
0
 
LVL 76

Expert Comment

by:David Lee
ID: 10697107
Since the application works okay on some PCs and not on others it seems unlikely that it's a problem with the code.  I'd expect the problem has something to do with the environment on the PCs where the app doesn't run properly.  Craig, I'm guessing that the app just fails to pull the data and that there are no error messages presented.  Is that correct?  Any chance the behavior you're seeing could be a permissions issue?  Do the users of the non-working PCs have sufficient rights to the database and the folder it's located in?  Who was logged in at the PC when testing the app on the problem PCs, you or the user?  I seem to remember reading somewhere that it's best if the same version of MDAC used on the development PC is installed on the client PCs.  Would it be possible to upgrade the version of MDAC on all the PCs?
0
 

Author Comment

by:cas27
ID: 10701373
BlueDevilFan,

I am in agreement with you.  THe code should be ok as it works on every PC I have tried  except for the two running the French localized version of Windows.

The MDAC is now the same on all of the PCs (2.8).  I have yet to give them the updated/compiled version off my PC, with MDAC 2.8.  The users were logged in as themselves - I had not thought of file permissions I will check that out.  


Sethi,

appProjectID is numeric, so you are right, I don't need the quotes.  Also, there is a space in the actual code - I must've forgotten to hit the space bar when I was cleaning it up to post.


Thanks all.  I'll let you know more after I try logging on as myself / check file permissions.

-Craig
0
Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

 

Author Comment

by:cas27
ID: 10701383
BlueDevilFan,

I do receive an error message, but it is is the error I get when the DB is supposed to be corrupt.  I know the DB is fine I have copied a good version onto the machine (and checked the file properties to make sure it was the exact same size, etc.).   As a further test, I then copied it back to another PC and tried the app with the new (copied) DB.  It ran perfectly.  This is the reason why I am so stumped on this.

Thanks again for the ideas.  More later.

-Craig
0
 
LVL 76

Expert Comment

by:David Lee
ID: 10703305
What's the error message?  Many Microsoft error messages are somewhat generic and have multiple possible causes.  If we knew what the error message is that might help isolate the cause.
0
 

Author Comment

by:cas27
ID: 10710482
Ok...  Debugging remotely... sorry for taking so long between posts...

Error info:
err.Source: Nodes
err.Descrip: Invalid Key
err.Number: 35603

Any ideas?

Thanks!

-Craig
0
 

Author Comment

by:cas27
ID: 10710707
I should mention that the error occurs when I try to add a node to the tree control.

So, on when it hits this line

'lets add the root node first:   root = string var = "f2" |   strTitle = "Function XXXX"
tree.Nodes.Add , ,root, strTitle

it sends me to my error handler.

I have also tried it the   set nodeX = tree.nodes.add    way, but that fails too.  I cannot even add a node "manually" by hard coding the values above into the statement.

Thanks!

-Craig
0
 
LVL 76

Accepted Solution

by:
David Lee earned 500 total points
ID: 10712667
If you haven't already done so, check this article out.  It mentions a problem with numeric key values.  Pay special attention to the notes at the end of the article.  It makes a mention of the "language selected in Regional Settings".  Since the problem is occuring on machines that use French this seems like a likely candidate.

http://support.microsoft.com/default.aspx?kbid=204054
0
 

Author Comment

by:cas27
ID: 10719414
BlueDevilFan,

Thanks!  That articel helped me with the problem.  As soon as I changed my key from AX to AAX, where A is a letter and X is number, it worked great!  Now I just need to tweak the rest of the app.

Thanks to everyone else who weighed in on the topic - its appreciated.

-Craig
0
 
LVL 76

Expert Comment

by:David Lee
ID: 10720293
Cool.  Glad I could help.
0

Featured Post

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

Have you ever wanted to restrict the users input in a textbox to numbers, and while doing that make sure that they can't 'cheat' by pasting in non-numeric text? Of course you can do that with code you write yourself but it's tedious and error-prone …
Background What I'm presenting in this article is the result of 2 conditions in my work area: We have a SQL Server production environment but no development or test environment; andWe have an MS Access front end using tables in SQL Server but we a…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now