Solved

Access Version Issues

Posted on 2013-05-16
20
483 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

 
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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
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…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

726 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