Importing Problems

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am trying to set up a system that will automatically import an excel file into access and print a few results. The problems that I am having
1) I want it to automatically set a field to be the primary key somehow (can be code, thats fine, i just don't know that much about code
2) I also need it to set a control value for every record in a particular query to yes (or no or anything just so that the other table that it is linked to will have information for it and therefore will show all the new records, not just the ones that you have been accessing.

This will be done on a weekly basis. PLEASE HELP ME!!!!!!!!!!! ANY IS APPRECIATED!
 
Hi,

You can create a module with a function like this:
Function CreateKey()

Dim dbs As Database

Set dbs = CurrentDb

' Create the NewIndex index on the Employees table.
dbs.Execute "CREATE INDEX NewIndex ON YOURTABLE " _
& "(YOURFIELD) with Primary;"

dbs.Close

End Function

Then, you can have your macro run this code. That should
help with question 1.

For question 2, I hope I am reading it correctly. Sounds
like you already have a query, but just want to have a
field that has a YES or NO value in it.

In the query grid, create a new field:

Yourfld:"YES"

Hope this helps
-----Original Message-----
I am trying to set up a system that will automatically
import an excel file into access and print a few results.
The problems that I am having:
1) I want it to automatically set a field to be the
primary key somehow (can be code, thats fine, i just don't
know that much about code)
2) I also need it to set a control value for every record
in a particular query to yes (or no or anything just so
that the other table that it is linked to will have
information for it and therefore will show all the new
records, not just the ones that you have been accessing.)
This will be done on a weekly basis. PLEASE HELP
ME!!!!!!!!!!! ANY IS APPRECIATED!
 
Okay, on problem two, if i have two tables that have a
relationship between them and the same, but updated, data
in one that is updated every week (such as cars, vin
numbers, etc.) and i have another table that they are
linked to (maintenance, etc), if there is a NEW entry in
the one that is updated, the only way to get that number
into the maintenance table is to make a change in that
query which they are linked together in. Can i make a
macro make a simple change automatically so that the
numbers for the equipment go into the maintenance table
(because it would be considered a change).

Thanks for your help.

Kenneth
 
Problem 1 is not fixed. I just need to know how to make
a macro or code create a primary key out of a field that
already exists.
 
You want to go to modules, create a new one.
Copy this code into the new module.

Function CreateKey()
Dim dbs As Database
Set dbs = CurrentDb
'Create the NewIndex index on the Employees table.
dbs.Execute "CREATE INDEX *NewIndex* ON *YOURTABLE* " _
& "(*YOURFIELD*) with Primary;"
dbs.Close
End Function

The stuff surrounded by * needs to be replaced. *NewIndex*
is whatever you want to name your primary key.
*YOURTABLE* represents the table that you want to add the
primary key. *YOURFIELD* represents the field that you
want to be the primary key.

Then, create a new macro, and select runcode. There will
be a place where you can select the function that you want
to run. Select the function that you just built in your
new module. I called mine CreateKey().

Hope this helps. I'll read your other post again tomorrow
and try to help if no one else has already answered it.
 
Can you make an append query to get the information that
you want into your other table? If so, you can run this
query from the macro.
 
Sorry for the delay...i have been out of town for a while and now i am back on track. I have no idea what an "append query" is. Can you help me?
 
Back
Top