Renaming Tables and Queries in Access Can Crash Your Database

While Access gives you the option to rename tables, queries, and other objects when you right click on them, you should avoiding doing so. After an object is renamed, you may receive an error such as this one:

Part of the trouble stems from the fact that other objects in the database will have trouble finding renamed objects that they have been linked to. If you select the option to 'Track name AutoCorrect info', and 'Perform name AutoCorrect' under File . . . .Options . . . Current Database, Access can avoid errors by updating object names automatically. But you need to give Access time to create the maps. Clear these options, close the database, reopen it, and re-select the options in order to make sure the maps are up to date.

Note the AutoCorrect feature will not revise object name changes in VBA code or queries written in SQL.

Parsing Data in an Access Column Into Multiple New Columns

The Tip of the Night for last night, showed how to parse data in an Access column. The code I posted specifically acted to pull data to the left of a delimiter. Here's how to parse out the data on both sides of a delimiter into two new columns.

In an example like this one, we have city and state names separated with tilde delimiters.

In the SQL mode of a query design view enter this code:

SELECT Table2.Field1, Trim(querysplit([Field1],"~",0)) AS Field2, Trim(querysplit([Field1],"~",1)) AS Field3 FROM Table2; We modify the number in the Trim function by entering a number to indicate the sequence of the text after a delimiter that we want to pull (so, 'querysplit([Field1],"~",1) pulls the first string after the ~ delimiter.)

We combine the two Trim functions by simply putting a comma between them on the line of code beginning with SELECT.

Note that as explained in last night's tip we need to have the following VBA code inserted in a module in order for this query to work:

Function QuerySplit(FieldName As String, Delim As String, Position As Integer) QuerySplit = Split(FieldName, Delim)(Position) End Function

When this query is run we end up with this result with the city and state names parsed into separate columns.

Text to Columns in an Access Table

You can parse data in an Access table as you would with the Text to Columns tool in MS Excel using the split function in a query. See the VBA code posted to this site by sxschech.

Start with a table like this one, which has delimiters (such as ~) which you want to use as a reference to separate data to the left of delimiter into the new column at the right.

Insert this VBA code in a new module:

Option Compare Database

Function QuerySplit(FieldName As String, Delim As String, Position As Integer) QuerySplit = Split(FieldName, Delim)(Position) End Function

Create a new query and select the SQL view.

Enter these SQL commands:

SELECT Field1, Trim(querysplit([Field1],"~",0)) AS Field2 FROM Table1;

We select the field to be parsed:

SELECT Field1,

. . . trim by the delimiter:

Trim(querysplit([Field1],"~",0))

. . . designate the new field to hold the extracted data,

AS Field2

. . . and reference the table to run the query in:

FROM Table1;

Run the query and the data will be parsed: