Link to home
Create AccountLog in
Avatar of JArndt42
JArndt42Flag for United States of America

asked on

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

   ''' 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
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")

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

    If ERR.Number = 70 Then
        Exit Sub
        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)
    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
        TableExists = False
    End If

End Function

Open in new window

Avatar of Patrick Matthews
Patrick Matthews
Flag of United States of America image

Try qualifying your references to the DAO classes.  For example, instead of:

Dim foo As Recordset

always use

Dim foo As DAO.Recordset


Dim foo As ADODB.Recordset

as the case may be...
Avatar of JArndt42


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.
Well that didn't work I saved it as a ACCCDB and it came with the same problem.
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"

Cap, I get an error, Variable Not defined, o dbVersion120 line.
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)


End Sub
There was no error on that one.
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
upload the database with the error
yea I cant do that cap. way too big and has way too many dlls needed and so forth to even open.
ok.  do this
1. comment all the codes from "Sub CreateTempTables"
     - correct any errors raised

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

run the codes

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

copy the image of your references and post here.
same compile error with 140. Here is the references.User generated image
you are using A2003 not A2010.

you cannot make a .ACCDB file from A2003

you can only use dbVersion40 in A2003
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?
Avatar of Rey Obrero (Capricorn1)
Rey Obrero (Capricorn1)
Flag of United States of America image

Link to home
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Not what I wanted to hear but thank you. Time for me to upgrade even though I do not like the new look.
i hear you :-0  ,but you'll get use to it.