Importing/Merging data help

[Darsarin]

HI,

First let me say my access knowledge is limited so bare with me. I have a simple 1 table DB that I am trying to import a excel sheet into.

Here is a screen shot of the DB.

and here is a shot of the excel sheet.

Both are lists of streets. The excel sheet is an updated list with all the streets in the DB plus some new ones. I want to import the excel sheet but exclude during the import all the streets that are already present in the db.

I have imported the excel sheet into a 2nd table called Streets and have tried to run an append query with no luck.

Here is the query I tried to use.

INSERT INTO msag (Street Name)
SELECT NewStreet Name FROM streets
WHERE NewStreet Name not in (SELECT msag.Street Name FROM msag);

If anyone could be of help it would be great.

 

[jackd]

Done, not many of them. Still getting:



I tried Street name, low and High all individually and got the error on all fields.

You can not add the primary key by individually creating an index for each field.

Start by removing the index.

Then create a composite index. All 3 pieces at the same time.

 

[Darsarin]

You can not add the primary key by individually creating an index for each field.

Start by removing the index.

Then create a composite index. All 3 pieces at the same time.

Tried that and got the error. Then tried each field to see which one was giving the error and they all will.

 

[jackd]

Tried that and got the error. Then tried each field to see which one was giving the error and they all will.

I can send a small procedure that will identify the indexes in your database.
Do you know how to run a procedure? Not trying to be smart, just want to make sure.

Post back before running this.




'
'=========================================================
'---------------------------------------------------------------------------------------
' Procedure : fjListPrimaryKeysNoLog
' DateTime : 2006-06-09 11:54
' Author : drawbrij
' Purpose : List non-System tables in this database with
' indexed fields; and
' primary keys and the fields/columns of those keys.
'---------------------------------------------------------------------------------------
'
Sub fjListPrimaryKeysNoLog()
Dim i As Integer ' for testing

Dim x As String
Dim idxLoop As Variant
Dim db As DAO.Database
Dim hName As String
Dim tdf As DAO.TableDef
Dim fld As DAO.Field
Set db = CurrentDb()
i = 0 'for testing
hName = "zzzqqq"
x = "List Tables with Indexes and/or " & vbCrLf & "Primary Key and Display Components" & vbCrLf & vbCrLf
Debug.Print x

Debug.Print CurrentDb.Name & vbCrLf & vbCrLf & "Table" & vbCrLf

For Each tdf In db.TableDefs
i = i + 1
If Left(tdf.Name, 4) <> "Msys" Then

For Each idxLoop In tdf.Indexes
If tdf.Name <> hName Then
hName = tdf.Name
Debug.Print vbCrLf
Else
End If
Debug.Print tdf.Name & " " & "Index = " & idxLoop.Name
For Each fld In idxLoop.Fields
Debug.Print " Contains field: " & fld.Name
Next fld
Next idxLoop
End If

Next tdf
End Sub

 

[Darsarin]

Do you know how to run a procedure? Not trying to be smart, just want to make sure.

Nope but I'm game for anything.

 

[jackd]

Nope but I'm game for anything.

Did you make a backup copy of your table?
You should do that if you haven't.

On the Main window for Access when you open your database, there is a list of Objects
including Tables, Queries etc. Modules

Copy the Procedure I gave previously from the first line starting with '
down to End Sub

(Select the text, then click ctrl B)

Then select the Modules object, click New and a new window will open.
Move your mouse pointer to that new window,
then click ctrl V. That will paste the procedure into that module.

Let's see if that works.

 

[jackd]

How's it going?

 

[Darsarin]

On the Main window for Access when you open your database, there is a list of Objects
including Tables, Queries etc. Modules

No modules listing. Have Tables, Queries and Reports. FYI I hate the new "Ribbon" junk in the latest office release.

 

[jackd]

No modules listing. Have Tables, Queries and Reports. FYI I hate the new "Ribbon" junk in the latest office release.

OK I have 2003, I do not have Office/Access 2007.
I'm not familiar with the interface.
Somewhere there will be a modules area, but I'm not sure where in 2007.

 

[jackd]

OK I have 2003, I do not have Office/Access 2007.
I'm not familiar with the interface.
Somewhere there will be a modules area, but I'm not sure where in 2007.

Click the Create tab on the Ribbon
In the Other group on the Create tab, click the arrow on the New Object button (with the ScreenTip “New Object: Module,” ), and then click Module


I found this at http://www.google.ca/search?hl=en&q=access+2007+modules+tab&btnG=Google+Search&meta=

 

[Darsarin]

Get a compile error User-defined type not defined

and high lights this: Dim db As DAO.Database