• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 498
  • Last Modified:

Access Version Issues

I have a problem. In my office we have multiple versions of Access. The code I will attach here was developed in 2003. it works in some versions but apparently not 2010 or above. In a nut shell what it does is create a new database, create a new table in the database and then links the table. The reason I do this is because the information I need is from a pass through query and I can not have a sub form based on a pass through. Therefore in order to prevent database bloat I create the new database. I don't have to do it this way but it is what works for me, until recently, so if anybody has a better way of doing what I want please feel free to share that too.
Sub CreateTempTables()
Dim strTempDatabase As String
' This subroutine illustrates how to use a temporary MDB in your app.
' If the temporary MDB is present then delete it.
'    The name of the temporary MDB created is the same as the current Front End (FE) name with
'    " temp" added to the end of the name.
' Create the temporary MDB.
' Create the temporary table(s) required in the temporary MDB.
' Link to those tables within your current FE
'   Do whatever you want
' Unlink the tables'
' Delete the temporary MDB


Dim tdfNew As TableDef, rs As Recordset
Dim wrkDefault As Workspace
Dim dbsTemp As Database
Dim strTableName As String

On Error GoTo LogError
' Get default Workspace.
Set wrkDefault = DBEngine.Workspaces(0)
strTempDatabase = Left$(CurrentDb.NAME, Len(CurrentDb.NAME) - 4) & "temp.mdb"

' Make sure there isn't already a file with the name of
' the new database.

If Dir(strTempDatabase) <> "" Then Kill strTempDatabase

'Create a new temp database
Set dbsTemp = wrkDefault.CreateDatabase(strTempDatabase, dbLangGeneral)
strTableName = "tblCarrierLoadHistory"

'strBracketedTableName = "[" & strTableName & "]"
' Delete the link to the temp table if it exists

If TableExists(strTableName) Then
   CurrentDb.TableDefs.Delete strTableName
End If
    
' Create the temp table
Set tdfNew = dbsTemp.CreateTableDef(strTableName)
With tdfNew
    .Fields.Append .CreateField("BCCARR", dbText, 8)
    .Fields.Append .CreateField("Origin", dbText, 30)
    .Fields.Append .CreateField("Dest", dbText, 30)
    .Fields.Append .CreateField("ORCOMC", dbText, 6)
    .Fields.Append .CreateField("Rev", dbDouble)
    .Fields.Append .CreateField("BAmt", dbDouble)
    .Fields.Append .CreateField("ORODR#", dbText, 7)
    .Fields.Append .CreateField("NegDate", dbDate)
    dbsTemp.TableDefs.Append tdfNew
End With
dbsTemp.TableDefs.Refresh

   ''' Link to the Import tables in the temp MDB ''''''
Dim tdfLinked As TableDef
Set tdfLinked = CurrentDb.CreateTableDef(strTableName)
tdfLinked.Connect = ";DATABASE=" & strTempDatabase
tdfLinked.SourceTableName = strTableName
CurrentDb.TableDefs.Append tdfLinked
CurrentDb.TableDefs.Refresh
RefreshDatabaseWindow
Set rs = CurrentDb.OpenRecordset(strTableName, dbOpenDynaset, dbAppendOnly)
    
'write the results of the query into the table
CurrentDb.Execute ("INSERT INTO tblCarrierLoadHistory Select PTCustomerService.* from PTCustomerService")
CurrentDb.TableDefs.Refresh
    

    
'     'Unlink the tables
'    CurrentDb.TableDefs.Delete strTableName
'
'     'Delete the temp mdb
'    strTempDatabase = Left$(CurrentDb.NAME, Len(CurrentDb.NAME) - 4) & " temp.mdb"
'    Kill (strTempDatabase)
ExitPoint:
    On Error GoTo 0
    rs.Close
    dbsTemp.Close
    Set rs = Nothing
    Set dbsTemp = Nothing
    Exit Sub
LogError:

    If ERR.Number = 70 Then
        Exit Sub
    Else
        Call LogError(ERR.Number, ERR.Description, "Create Tables" & " Create Load History Tables", , True)
        Resume ExitPoint
    End If

    
End Sub

Function TableExists(strTableName As String) As Integer
    ' Checks for the existance of a specific table

    'Added the tabledefs.refresh as tables just added in this session weren't
    '   being picked up.

    Dim dbDatabase As Database, tdefTable As TableDef

    On Error Resume Next
    Set dbDatabase = DBEngine(0)(0)
    dbDatabase.TableDefs.Refresh
    Set tdefTable = dbDatabase(strTableName)
    
    ' If an error occurred the tabledef object could not be accessed and
    ' therefore doesn't exist. This could cause problems in a secured environment
    ' though as access may be denied.
    If ERR = 0 Then
        TableExists = True
    Else
        TableExists = False
    End If

End Function

Open in new window

0
JArndt42
Asked:
JArndt42
  • 11
  • 8
1 Solution
 
Patrick MatthewsCommented:
Try qualifying your references to the DAO classes.  For example, instead of:

Dim foo As Recordset

always use

Dim foo As DAO.Recordset

or

Dim foo As ADODB.Recordset

as the case may be...
0
 
JArndt42Author Commented:
Good advice. However that did not work. The error I get is that you can not link to table in a different version of access or excel. I am going to try and save it in the latest version and see if I can go backwards.
0
 
JArndt42Author Commented:
Well that didn't work I saved it as a ACCCDB and it came with the same problem.
0
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
Rey Obrero (Capricorn1)Commented:
try this revision, see the bold parts


Set wrkDefault = DBEngine.Workspaces(0)
strTempDatabase = Left$(CurrentDb.NAME, Len(CurrentDb.NAME) - 4) & "temp.accdb"

' Make sure there isn't already a file with the name of
' the new database.

If Dir(strTempDatabase) <> "" Then Kill strTempDatabase

'Create a new temp database
Set dbsTemp = wrkDefault.CreateDatabase(strTempDatabase, dbLangGeneral,dbVersion120)
strTableName = "tblCarrierLoadHistory"



.
0
 
JArndt42Author Commented:
Cap, I get an error, Variable Not defined, o dbVersion120 line.
0
 
Rey Obrero (Capricorn1)Commented:
copy this and run, see if you still get the error


Sub CreateACCDB()
Dim dbName As String, db As DAO.Database
dbName = "MySample.accdb"
Set db = DBEngine.CreateDatabase(CurrentProject.Path & "\" & dbName, dbLangGeneral, dbVersion120)

db.Close


End Sub
0
 
JArndt42Author Commented:
There was no error on that one.
0
 
JArndt42Author Commented:
but the odd thing is I put it in another database and it has the error.
When I put it into a new data base no error. something does not like the dbVerision120
0
 
Rey Obrero (Capricorn1)Commented:
upload the database with the error
0
 
JArndt42Author Commented:
yea I cant do that cap. way too big and has way too many dlls needed and so forth to even open.
0
 
Rey Obrero (Capricorn1)Commented:
ok.  do this
1. comment all the codes from "Sub CreateTempTables"
2. Do DEBUG > COMPILE
     - correct any errors raised


3. UN-comment all the codes from "Sub CreateTempTables"
    Do DEBUG > COMPILE
     - correct any errors raised

run the codes

post back when you are done.
0
 
JArndt42Author Commented:
when commented out no compile errors. When un-commented the compile error goes to DBVersion120 and says "Variable Not Defined"
0
 
JArndt42Author Commented:
am I missing a reference possibly?
0
 
Rey Obrero (Capricorn1)Commented:
change DBVersion120  with DBVersion140

copy the image of your references and post here.
0
 
JArndt42Author Commented:
same compile error with 140. Here is the references.References
0
 
Rey Obrero (Capricorn1)Commented:
you are using A2003 not A2010.

you cannot make a .ACCDB file from A2003


you can only use dbVersion40 in A2003
0
 
JArndt42Author Commented:
so basically there is no way to do what I want to do with other versions then huh? At least the one I am using now huh?
0
 
Rey Obrero (Capricorn1)Commented:
no,
you need A2007 or higher to create a .ACCDB database
0
 
JArndt42Author Commented:
Not what I wanted to hear but thank you. Time for me to upgrade even though I do not like the new look.
0
 
Rey Obrero (Capricorn1)Commented:
i hear you :-0  ,but you'll get use to it.
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 11
  • 8
Tackle projects and never again get stuck behind a technical roadblock.
Join Now