Solved

Access Version Issues

Posted on 2013-05-16
20
462 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
 
LVL 119

Expert Comment

by:Rey Obrero
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 119

Expert Comment

by:Rey Obrero
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 119

Expert Comment

by:Rey Obrero
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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 119

Expert Comment

by:Rey Obrero
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 119

Expert Comment

by:Rey Obrero
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 119

Expert Comment

by:Rey Obrero
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 119

Accepted Solution

by:
Rey Obrero 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 119

Expert Comment

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

Featured Post

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
Deploying a Microsoft Access application in a Citrix environment is not difficult but takes a few steps. However, Citrix system people are often of little help, as they typically know next to nothing about Access. The script provided here will take …
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…

707 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

15 Experts available now in Live!

Get 1:1 Help Now