Splitting tables

MS Office Access


  • 1. CurrentProject.Connection.Execute
    hello i have a good ms access application that has been working for 12 months sometimes i get a problem with invalid code that i fix with /decompile and reimport modules from previous working backup but now any call to CurrentProject.Connection.Execute results in ms access shutting down completely there has been no development work on this database for months i have created a new database project with one table and get the same results therefore the problem is specific to access - not just my database i am thinking there has been a microsoft update that has knackered access or ado or something i have done all the "usual tricks" including repairing and reinstalling access but this time no luck whatsoever has anyone had same experience or can offer ideas ? all the best john
  • 2. How can I get a Windows 2000 server date
    Dear all, I have an application which I don't want to use the client's pc system date/time to update database fields, I want to get the date/time from my Windows 2000 server instead. Can anyboby show me how to archieve this? thanks!
  • 3. Is it possible?
    Is it possible to transpose Table 1 to Table 2? TABLE 1 Start_Date Course Duration 08/05/04 A 2 09/05/04 B 1 09/05/04 C 3 08/05/04 D 3 10/05/04 E 2 TABLE 2 08/05/04 09/05/04 10/05/04 11/05/04 A A B C C C D D D E E
  • 4. PDF form fields
    I am working on an HR application. Front-end MS Access backend SQL Server. One of the requirements involves interacting with the IRS from w4 as a pdf. What I need to be able to do is read the employee info (i.e. name , address etc) from the database table and have the application open the W4 and automatically fill-in the form fields (i.e. name, address etc.) on the W4 form. Can any one point me to some samples for doing this? I downloaded the Adobe sdk but am having a heck of a time trying to decipher the documentation. Someone out there must be doing this. Help!!!!
  • 5. Create a check register in access for three accounts
    I maintain a access database that keeps track of the checks I write as a part of my work. We recently decided to have two other people create checks as a back-up to me (in case I am on vacation or otherwise not in) but management wants only 1 database to track all checks. Each cashier's start with check 101 but will not use them at the same rate but they cannot repeat a number. Also, the fee charged for using the check must be calculated and stored with each entry (but this rate is subject to change each October). What is the best option? Thanks.

Splitting tables

Postby THVjaWFubw » Tue, 01 Jul 2008 22:45:01 GMT

I saved my database as an .mdb file and I splitted my tables successfully. 
But when I save my database as an .accdb (Access 2007) database, it wont work 
anymore. Could it be an incompatibilty with my DAO-code?

Re: Splitting tables

Postby pietlinden@hotmail.com » Tue, 01 Jul 2008 23:09:51 GMT

Define "won't work".  What error message do you get?

Re: Splitting tables

Postby Chris O'C via AccessMonster.com » Wed, 02 Jul 2008 01:36:34 GMT

Could it be your db isn't in a trusted folder and your VBA code is disabled?

Microsoft MVP

Message posted via AccessMonster.com

Re: Splitting tables

Postby THVjaWFubw » Wed, 02 Jul 2008 04:00:01 GMT

Atfter splitting my tables in Access2007, The problem seems to occur in the 
following code:

Public ws As Workspace
Public db As Database
Public tbSaldos As Recordset
Set ws = DBEngine.Workspaces(0)
Set db = DBEngine.Workspaces(0).Databases(0)
Set tbSaldos = db.OpenRecordset("tblSaldos", DB_OPEN_TABLE)

The very moment this code is reached, my database is closed, without neither 
warning nor error message.
I think this code is still in DAO-syntax, but how to convert into ADO (if 
this is the problem); I have a huge lines of similar VBA-code.

Re: Splitting tables

Postby Chris O'C via AccessMonster.com » Wed, 02 Jul 2008 04:32:38 GMT

Is tblSaldos now a linked table?  It's probably crashing because it's getting
an unhandled runtime error when it tries to open the recordset as a table
type recordset if tblSaldos is a linked table.  If that's the case either
remove the DB_OPEN_TABLE parameter and let Jet choose the default or specify
dbOpenDynaset.  You may be able to use one of two other options,
dbOpenSnapshot or dbOpenForwardOnly, depending on your situation.

There's no error message when it crashes because there's no error handler in
your procedure or the procedures that call it.  Add error handling to your
procedures and you'll usually get some clue what's gone wrong.  Not
guaranteed though.

If your code uses both the DAO and ADO libraries, you need to tell Access
which library to use for the recordset objects and any other objects where
both libraries have the same name.  Example:

    Public tbSaldos As DAO.Recordset

Microsoft MVP

Message posted via  http://www.**--****.com/ 

Re: Splitting tables

Postby THVjaWFubw » Wed, 02 Jul 2008 05:19:03 GMT

Indeed, tblSaldos is a linked table. But I need this DB_OPEN_TABLE  parameter 
in many cases, together with an indexed field and the .seek method.
It will be quit complicated to convert all this code, but maybe there are no 

Re: Splitting tables

Postby Chris O'C via AccessMonster.com » Wed, 02 Jul 2008 06:57:35 GMT

You sound anxious to get started rewriting your app with ADO.  Don't you like
DAO?  A word of advice:  most things we use either library for have
equivalents, so unless there's functionality that ADO has that DAO doesn't
have, don't spend time on a rewrite on a functional db.

You can use the seek method on a linked table if you use a recordset object
that's opened on the linked table's database, not the current db.  Example:

Public Function linkedTblRS(strTableName As String) As DAO.Recordset

    On Error GoTo link_Err
    Dim db As Database
    Dim strDBName As String
    strDBName = Mid(CurrentDb.TableDefs(strTableName).Connect, 11)
    Set db = DBEngine.Workspaces(0).OpenDatabase(strDBName, False, False)
    Set linkedTblRS = db.OpenRecordset(strTableName, dbOpenTable)
    Set db = Nothing
    Exit Function
    MsgBox Err.Number & vbCrLf & Err.Description
    Resume link_Exit
End Function

Call the above function when you need to use seek on a linked table.  Like

Public Function seekInLinkedTbl()

    On Error GoTo seek_Err
    Dim rs As DAO.Recordset
    Dim varBookmark As Variant
    Dim strLookFor As String

    strLookFor = "Grapes of Wrath"
    Set rs = linkedTblRS("tblBooks")
    rs.Index = "PrimaryKey"
    varBookmark = rs.Bookmark
    rs.Seek "=", strLookFor
    If rs.NoMatch Then
        rs.Bookmark = varBookmark
        MsgBox "Not found"
        MsgBox "Found " & strLookFor
    End If
    Set rs = Nothing
    Exit Function

    MsgBox Err.Number & vbCrLf & Err.Description
    Resume seek_Exit

End Function

Microsoft MVP

Message posted via  http://www.**--****.com/ 

Re: Splitting tables

Postby THVjaWFubw » Wed, 02 Jul 2008 21:28:00 GMT

You solved my problem (I indeed had to open the linked table, not the current 
db). Thank you very much for your excellent advise.


Similar Threads:

1.Need query to split table into 2 linked tables

I have imported a table which is very big (over 200 columns). I want to split 
this table into two linked (in one-to-one relationship) by an autonumber ID. 
Is there an efficient way to do this? I would assume I can't write out table 
2 until table 1 has been completely written and the autonumber IDs assigned. 
Or is there a way to do it in a single query.

Table 1
ID autonumber

Table 2
ID links to ID in table 1

2.Split Table to multiple tables

Dear all,

I wanted to write a module to split a rather large table into multiple 
smaller tables.
i.e. source table has 200.000 records, and I want to split it into tables by 
blocks of i.e. 10.000 records.

The splitting is fairly simple: first 10.000 recs -> Table1, next 10.000 
recs -> Table2, ....
No particular order...

If coded a module that uses a recordset object and then cycles record by 
record, counting them and copying them in a new table until it reaches the 
max. number of records, but this is really slow !

So I wondered if there was an other method which is perhaps more efficient.


3.Unable to link to split table in Access 2002

The following code works in Access 2000, but does not 
work in Access 2002.  Error is ...

Method 'Connection' of object '_Current Project' failed.

Can you help me fix this?  I'm using Windows XP on both 


Private Sub Form_Open(Cancel As Integer)
On Error GoTo err_form_open

10  'Print CurrentProject.Connection: Stop

    Dim rst As New ADODB.Recordset
20  rst.Open "qryPropertyProfile", 
CurrentProject.Connection, _
        adOpenKeyset, adLockOptimistic
    'Debug.Print rst!PropertyCode
    txtTempPropCode.DefaultValue = rst!PropertyCode
    TxtTempPropName.DefaultValue = rst!PropertyName
    txtTempPropCode = rst!PropertyCode
    TxtTempPropName = rst!PropertyName
    'DoCmd.Close ("qryPropertyProfile")
    DoCmd.ShowToolbar "Form View", acToolbarNo
    DoCmd.GoToRecord , , acNewRec
    DoCmd.GoToControl "Location"
    'DoCmd.ShowToolbar "Menu Bar", acToolbarNo
    DoCmd.ShowToolbar "Web", acToolbarNo
    Exit Sub
 MsgBox Err.Description

 Resume exit_form_open
Exit Sub

End Sub

4.Packaging a split table

I need to package a split Access app so that I can provide application
updates in the future without stepping on the data previously saved at the
user site.
How do I set the pointers in Linked Table Manager so that the app will find
the data after packaging and installation?


5.Import Excel db and splitting table cells

Hi there,

I was pointed to a discussion in this forum about splitting tables, but I 
don't think that a code solution is what I need, although I would be willing 
to entertain it some more. I searched this group and I don't see anything 
that sounds like it would work for me.

I need to import an Excel worksheet into Access.

The data is all in one column, but seperated by commas (ie, apples,  
oranges, bananas).

I am aware of the text to columns function in Excel, but when I do this, I 
end up with 20+ columns and no way to make them all into one column.

Is there a way to import the data into Access and put each word into a 
single cell? Or is there a way in Excel I can do this? Right now I'm copying 
my Excel column and pasting it into Word, then replacing all the commas with 
paragraph tags so I can get a column of words.


6. Linked Split table file from QB ODBC not updating

7. Splitting tables

8. Split table containing data..possible in this case?

Return to MS Office Access


Who is online

Users browsing this forum: No registered users and 59 guest