Access 2010 XP vs Access 2010 Windows 7

[Lrn2Code]

Hello,

I have an Access 2007 (2010) database on my XP machine - 32 bit. The database creates other databases by taking data grouped by a union (a union can have multiple entities within it) and creates 1 database with that union and its multiple entities.

A co-worker has Access 2010 with Windows 7 - 32 bit - and when he runs the same database creation code he gets separate databases for each entity within the selected union.

We checked for any service pack differences and don't find any.

What would cause that to happen? The code is no different between my machine and his.

Thanks for your time and guidance.

Lrn2Code

 

[GregRun]

Are you using windows API? Can you post the offending code?

 

[Lrn2Code]

Hi GregRun,

I don't know about API. How/where do I check?

My boss just told me this user is having other issues with his laptop so...maybe it's not the code.

We're going to have other users in the office try it and see what happens.

The code is below, though, because something else might be amiss.

Thanks!

Lrn2Code

Sub runLoad()
 
Dim dbs As DAO.Database
Dim dbs2 As DAO.Database
Dim rst As DAO.Recordset
Dim rst2 As DAO.Recordset
Dim distID As String
Dim sql As String
Dim sql2 As String
Dim strRst As String
Dim sourceFile
Dim destinationFile
Dim regid As String
Dim AdminID As String
Dim strName As String
Dim Typ As String
Dim qdf As DAO.QueryDef
Dim qdf2 As DAO.QueryDef
 
Set dbs = CurrentDb
 
strRst = "select * from qry_VerifiedLoad where setup = true"
Set rst = dbs.OpenRecordset(strRst)
 
rst.MoveLast
rst.MoveFirst
 
DoCmd.Hourglass True 
 
Do Until rst.EOF 'Dists that are checked
       regid = rst.Fields("ID")
       distID = rst.Fields("OrgID")
       AdminID = rst.Fields("AdminID")
       strName = rst.Fields("OrgName")
       Typ = rst.Fields("Type")
 
      sourceFile = "D:\Documents and Settings\xxxxxxxxxxx\Desktop\statbook_Verified.mdb"  
 
      destinationFile = "D:\Documents and Settings\xxxxxxxxxxx\Desktop\LoadVerified\Statbook_Verified_" & AdminID & ".mdb"
 
 
       Dim strdcty As String
 
       strdcty = destinationFile
 
       If Len(Dir(strdcty)) = 0 Then
 
       FileCopy sourceFile, destinationFile
 
         End If
 
      Dim strLoadExp As String
 
strLoadExp = "Insert into d_expenditures in '" & destinationFile & "'  Select gradeLevCode, ObjCode, ProCode, FunctionCode, Amount, orgID from qry_4_VERIFIED_EXPEND_DONT_OPEN where orgid = '" & distID & "'"

       Dim qdfExp As DAO.QueryDef
       Set qdfExp = dbs.CreateQueryDef("", strLoadExp)
       qdfExp.Execute
 
    Dim strLoadRev As String
 
     strLoadRev = "Insert into d_revenues in '" & destinationFile & "'  Select RevCode, ProCode, FLev, Amount, orgID from qry_4_VERIFIED_REV_DONT_OPEN where orgid = '" & distID & "'"

       Dim qdfRev As DAO.QueryDef
       Set qdfRev = dbs.CreateQueryDef("", strLoadRev)
       qdfRev.Execute
 
          Slowdown          
 
  'NEED TO UPDATE RECORDS IN C_ORGS 12-14-10
   Dim strsqlsetup As String
   Dim rstset  As DAO.Recordset
   Dim setorg As String
 
   strsqlsetup = "Select * from qry_VerifiedLoad  where setup  = True"
   Set rstset = CurrentDb.OpenRecordset(strsqlsetup)
 
   If rstset.RecordCount > 0 Then
   rstset.MoveLast
   rstset.MoveFirst
 
   Do Until rstset.EOF
   setorg = rstset.Fields("OrgID")
 
 
sql = "Update c_orgs Set setup = True Where adminid='" & AdminID & "'"    CurrentDb.Execute sql, dbFailOnError
 
   rstset.MoveNext
   Loop
   End If
 
       Dim alset As String
       Dim qdfset As DAO.QueryDef
 
       ' ALREADYSETUP NEEDS TO BE TRUE OR VERIFIED DB WON'T WORK 9-30-09
       alset = "Update c_orgs in '" & destinationFile & "' Set AlreadySetup = True where adminid='" & AdminID & "'" 
       Set qdfset = dbs.CreateQueryDef("", alset)
       qdfset.Execute
 
 
       sql = "insert into D_Contacts in '" & destinationFile & "' select contact_Name, contact_Phone, contact_Email from D_Contacts where orgid  = '" & distID & "'"
       Set qdf = dbs.CreateQueryDef("", sql)
       qdf.Execute
 
      Slowdown  
 
       sql = "insert into d_stat_edits in '" & destinationFile & "' select * from d_stat_edits where orgid = '" & distID & "'"
       Set qdf = dbs.CreateQueryDef("", sql)
       qdf.Execute
 
 
       sql = "insert into D_SW1 in '" & destinationFile & "' select * from D_SW1 where orgid = '" & distID & "'"
       Set qdf = dbs.CreateQueryDef("", sql)
       qdf.Execute
 
       sql = "insert into D_SW2 in '" & destinationFile & "' select * from D_SW2 where orgid = '" & distID & "'"
       Set qdf = dbs.CreateQueryDef("", sql)
       qdf.Execute
 
       sql = "insert into D_SW3 in '" & destinationFile & "' select * from D_SW3 where orgid = '" & distID & "'"
       Set qdf = dbs.CreateQueryDef("", sql)
       qdf.Execute
 
       sql = "insert into D_SW4 in '" & destinationFile & "' select * from D_SW4 where orgid = '" & distID & "'"
       Set qdf = dbs.CreateQueryDef("", sql)
       qdf.Execute
 
       sql = "insert into D_SW5 in '" & destinationFile & "' select * from D_SW5 where orgid = '" & distID & "'"
       Set qdf = dbs.CreateQueryDef("", sql)
       qdf.Execute
 
       sql = "insert into D_SW6 in '" & destinationFile & "' select * from D_SW6 where orgid = '" & distID & "'"
       Set qdf = dbs.CreateQueryDef("", sql)
       qdf.Execute
 
       sql = "insert into D_SW7 in '" & destinationFile & "' select * from D_SW7 where orgid = '" & distID & "'"
       Set qdf = dbs.CreateQueryDef("", sql)
       qdf.Execute
 
       sql = "insert into D_SW8 in '" & destinationFile & "' select * from D_SW8 where orgid = '" & distID & "'"
       Set qdf = dbs.CreateQueryDef("", sql)
       qdf.Execute
 
       sql = "insert into D_SW9 in '" & destinationFile & "' select * from D_SW9 where orgid = '" & distID & "'"
       Set qdf = dbs.CreateQueryDef("", sql)
       qdf.Execute
 
       sql = "insert into D_SW10 in '" & destinationFile & "' select * from D_SW10 where orgid = '" & distID & "'"
       Set qdf = dbs.CreateQueryDef("", sql)
       qdf.Execute
 
       sql = "insert into tbl_recap_dataentry in '" & destinationFile & "' select * from tbl_recap_dataentry where orgid = '" & distID & "'"
       Set qdf = dbs.CreateQueryDef("", sql)
       qdf.Execute
 
       sql = "insert into util_opened_exps in '" & destinationFile & "' select * from util_opened_exps where orgid = '" & distID & "'"
       Set qdf = dbs.CreateQueryDef("", sql)
       qdf.Execute
 
       sql = "insert into util_opened_revs in '" & destinationFile & "' select * from util_opened_revs where orgid = '" & distID & "'"
       Set qdf = dbs.CreateQueryDef("", sql)
       qdf.Execute
 
       Set dbs2 = DBEngine.Workspaces(0).OpenDatabase(destinationFile)
 
       sql2 = "update d_expenditures in '" & destinationFile & "' set adminid = '" & AdminID & "'"
       Set qdf2 = dbs2.CreateQueryDef("", sql)
       qdf.Execute
 
       sql2 = "update d_revenues in '" & destinationFile & "' set adminid = '" & AdminID & "'"
       Set qdf2 = dbs2.CreateQueryDef("", sql)
       qdf.Execute
 
  'POPULATE ALL ADMINIDS 10-15-09
   Dim strnoadmin As String
   Dim rstnoadmin As DAO.Recordset
 
   strnoadmin = "select * from d_expenditures where IsNull(adminid)"
   Set rstnoadmin = CurrentDb.OpenRecordset(strnoadmin)
 
   If rstnoadmin.RecordCount > 0 Then
       sql2 = "Update d_expenditures in '" & destinationFile & "' set adminid = '" & AdminID & "'"
       Set qdf2 = CurrentDb.CreateQueryDef("", sql2)
       qdf2.Execute
   End If
 
   Dim strnoadmin1 As String
   Dim rstnoadmin1 As DAO.Recordset
 
   strnoadmin1 = "select * from d_revenues where IsNull(adminid)"
   Set rstnoadmin1 = CurrentDb.OpenRecordset(strnoadmin1)
 
   If rstnoadmin1.RecordCount > 0 Then
       sql2 = "Update d_revenues in '" & destinationFile & "' set adminid = '" & AdminID & "'"
       Set qdf2 = CurrentDb.CreateQueryDef("", sql2)
       qdf2.Execute
   End If
 
'END OF POPULATE ALL ADMINIDS 10-15-09
 
sql = "UPDATE c_orgs SET c_orgs.setup = False where Orgid = '" & distID & "'"   'CLEAR OUT c_orgs - SO NEXT RECORD WILL LOAD 1-4-12
Set qdf = dbs.CreateQueryDef("", sql)
qdf.Execute
 
rst.MoveNext
Loop
 
DoCmd.Hourglass False
 
MsgBox ("The district(s) you selected have been loaded.")
 
sql = "UPDATE c_orgs SET c_orgs.setup = False"
Set qdf = dbs.CreateQueryDef("", sql)
qdf.Execute
End Sub__________________

 

[Lrn2Code]

An update - the second user that ran the database had the same issue of multiple databases instead of 1 being created. This user is also on Windows 7 so I'm thinking that there is an issue with Access 2010 and Windows 7.

Lrn2Code

 

[GregRun]

If you were using API, you would probably know it. Doesn't look like it here.

Might be that part of your problem is that in Windows 7, the user's desktop is by default located at C:\Users\[Username]\Desktop instead of "Documents and Settings." You can get the user directory more reliably like this:

sourceFile = Environ("USERPROFILE") & "\Desktop\statbook_Verified.mdb"

If that doesn't fix your problem, try to step through the code on the Windows 7 machine and watch your destination folder and see when it is creating databases that it shouldn't create.

I'm very interested in this topic because my office will be upgrading from Windows XP to Windows 7 THIS WEEK! Let me know if this helps!

 

[Lrn2Code]

Thanks for the sourcefile code change. I will check that out, and yes, I'll let you know if this helps.

Thanks again!

Lrn2Code

 

[GregRun]

Make sure you make the change to destinationFile as well

 

[gemma-the-husky]

assuming it is somethnig to do with destinationfile - it may be a W7 issue

when you copy/paste files in W7, you get the added option to create a new version (which you didn;t get in XP)

so you end up with

file.dat
file(2).dat
fiel(3).dat

etc

do the dbs's have that sort of name structure?

 

[Lrn2Code]

Good Day,

Had a second user with W7 test the build using Environ("USERPROFILE"). Unfortunately multiple databases were still created and they are listed by the orgid not the file.dat format. There is supposed to be 1 database with the SU in the name of the file and all of the orgids related to that SU within that 1 database.

There must be something else going on in W7 that is causing multiple databases to be built. Am not sure how my code should be structured to address this problem.

I'm searching the Web about this but so far not finding anything.

Any other ideas?

Thanks,

Lrn2Code

 

[GregRun]

What about Dave's suggestion? Is that how your files are coming out?

I'm having a hard time following your code. From what I'm looking at, it doesn't make sense why this ISN'T creating multiple files on your machine. Can you post your database?

 

[GregRun]

I've got some general tips for you that I'll post later but I just noticed this:

If rstset.RecordCount > 0 Then
   rstset.MoveLast
   rstset.MoveFirst
 
   Do Until rstset.EOF
   setorg = rstset.Fields("OrgID")
 
 
sql = "Update c_orgs Set setup = True Where adminid='" & AdminID & "'"    CurrentDb.Execute sql, dbFailOnError
 
   rstset.MoveNext
   Loop
   End If

You've got your End If outside of your loop. That needs to be (probably) right after your MoveLast statement.

EDIT: Nevermind... Missed that extra Do in there.

 

[Lrn2Code]

Regarding Dave's suggestion...no there are not multiple .dat files; they are actual databases being created.

The thing that doesn't make sense to me is that it works perfectly on my XP machine...one database with all the necessary orgs in it.

Has VB changed in Windows 7? I know there are differences between VB code for 64 bit and 32 bit systems.

 

[GregRun]

Went through your code a little more thoroughly. Found a few issues:

Example 1 Concepts:
1. Don't Dim variables in the middle of your code. It makes it difficult to read. Put all of your variable declarations in the top of your sub.
2. Use consistent indentation. For example, your If and the matching Else and End If for it should be indented the same number of spaces. Everything inside of that statement should be indented further.
3. Don't create extra variables if you don't need them.
4. Try not to use more functions than necessary.

Examples of these are illustrated very well in the following small chunk of your code

Your code:

'...
       Dim strdcty As String
 
       strdcty = destinationFile
 
       If Len(Dir(strdcty)) = 0 Then
 
       FileCopy sourceFile, destinationFile
 
         End If
'...

My version:

    If Dir(destinationFile) = "" Then
        FileCopy sourceFile, destinationFile
    End If

I'll give you a break on the Len(Dir(...))=0 thing. I've seen plenty of people do that. It's just not necessary.

I'm ridiculously anal about indenting because it makes it easier for me to go back later and figure out what the hell I was trying to do. I would recommend using the Edit toolbar in the VB Editor. It will let you indent/outdent entire sections of code easily (which you can also do by selecting the block of code and using tab or shift+tab) AND will let you comment or uncomment entire blocks of code as well. It will take you MAYBE 5 minutes to outdent everything all the way to the left and then indent where necessary. It will help you see what you code is doing.


Example 2 Concepts:
1. You don't need to create a querydef to run SQL code.
2. As above, use one string variable (Like you did later in the code with "sql") to store the text of your query. You don't need to Dim out a new variable unless you're going to use it later.

Your code:

      Dim strLoadExp As String
 
strLoadExp = "Insert into d_expenditures in '" & destinationFile & "'  Select gradeLevCode, ObjCode, ProCode, FunctionCode, Amount, orgID from qry_4_VERIFIED_EXPEND_DONT_OPEN where orgid = '" & distID & "'"

       Dim qdfExp As DAO.QueryDef
       Set qdfExp = dbs.CreateQueryDef("", strLoadExp)
       qdfExp.Execute
 
    Dim strLoadRev As String
 
     strLoadRev = "Insert into d_revenues in '" & destinationFile & "'  Select RevCode, ProCode, FLev, Amount, orgID from qry_4_VERIFIED_REV_DONT_OPEN where orgid = '" & distID & "'"

       Dim qdfRev As DAO.QueryDef
       Set qdfRev = dbs.CreateQueryDef("", strLoadRev)
       qdfRev.Execute

My version:

    sql = "Insert into d_expenditures in '" & destinationFile & "'  Select gradeLevCode, ObjCode, ProCode, FunctionCode, Amount, orgID from qry_4_VERIFIED_EXPEND_DONT_OPEN where orgid = '" & distID & "'"
    dbs.Execute sql
    
    sql = "Insert into d_revenues in '" & destinationFile & "'  Select RevCode, ProCode, FLev, Amount, orgID from qry_4_VERIFIED_REV_DONT_OPEN where orgid = '" & distID & "'"
    dbs.Execute sql

It won't take you very long to make these changes and it will decrease the chance of typos or other mistakes later. Also, make sure you have this at the very top of your module:

Option Compare Database
Option Explicit

This will also help you avoid little mistakes that are an absolute bastard to find later.

 

[Lrn2Code]

GregRun,

Thank you very much for your suggestions. I will go through the code to organize and clean it up.

Not sure if that will address the multiple database creation issue, but is worth a try.

 

[GregRun]

If it still doesn't work once you have the code fixed then post your code again.

 

[Lrn2Code]

Good news, after cleaning up the code and changing the order of execution things are working as expected in W7.

Which makes me wonder why it would have worked correctly in XP but not W7 to begin with.

Oh well...thank you very much for your guidance and follow through.

Hopefully your upgrade to W7 goes smoothly.

Have a great weekend!