Solved

Access Version Issues

Posted on 2013-05-16
20
469 Views
Last Modified: 2013-05-17
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
Comment
Question by:JArndt42
  • 11
  • 8
20 Comments
 
LVL 92

Expert Comment

by:Patrick Matthews
ID: 39172496
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
 
LVL 1

Author Comment

by:JArndt42
ID: 39172586
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
 
LVL 1

Author Comment

by:JArndt42
ID: 39172667
Well that didn't work I saved it as a ACCCDB and it came with the same problem.
0
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 39172747
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
 
LVL 1

Author Comment

by:JArndt42
ID: 39172788
Cap, I get an error, Variable Not defined, o dbVersion120 line.
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 39173014
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
 
LVL 1

Author Comment

by:JArndt42
ID: 39173233
There was no error on that one.
0
 
LVL 1

Author Comment

by:JArndt42
ID: 39173250
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
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 39173360
upload the database with the error
0
 
LVL 1

Author Comment

by:JArndt42
ID: 39175022
yea I cant do that cap. way too big and has way too many dlls needed and so forth to even open.
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 39175063
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
 
LVL 1

Author Comment

by:JArndt42
ID: 39175103
when commented out no compile errors. When un-commented the compile error goes to DBVersion120 and says "Variable Not Defined"
0
 
LVL 1

Author Comment

by:JArndt42
ID: 39175105
am I missing a reference possibly?
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 39175132
change DBVersion120  with DBVersion140

copy the image of your references and post here.
0
 
LVL 1

Author Comment

by:JArndt42
ID: 39175151
same compile error with 140. Here is the references.References
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 39175163
you are using A2003 not A2010.

you cannot make a .ACCDB file from A2003


you can only use dbVersion40 in A2003
0
 
LVL 1

Author Comment

by:JArndt42
ID: 39175188
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
 
LVL 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 500 total points
ID: 39175208
no,
you need A2007 or higher to create a .ACCDB database
0
 
LVL 1

Author Closing Comment

by:JArndt42
ID: 39175218
Not what I wanted to hear but thank you. Time for me to upgrade even though I do not like the new look.
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 39175239
i hear you :-0  ,but you'll get use to it.
0

Featured Post

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

As with any other System Center product, the installation for the Authoring Tool can be quite a pain sometimes. This article serves to help you avoid making these mistakes and hopefully save you a ton of time on troubleshooting :)  Step 1: Make sur…
Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…

773 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