Updating Tables

  • Thread starter Thread starter Doug
  • Start date Start date
D

Doug

Hello All,

I'm new to programming in access. Most of my code has been in VB, using ADO
with a SQL Server back end.

I have an excel spread sheet with employees that needs to get imported each
month into a table. I want to create a button and allow the user to handle
the import. One approach would be to delete the entire table and slam
everything from the spreadsheet in.

I'd prefer to insert only the new employees. I'd really like to just execute
SQL in the form, but I'm finding this cumbersome. I see other code that
creates a DAO connection, to accomplish what I want. That seems rather
clunking in that your going outside the DB and then back in.

Can't I just run some SQL code against a table? Do I have to create an
Update Query and use that? Again, I'd prefer just to run straight code,
similar to what I do in VB.

If someone has a good website, that would help also.

Appreciate any help.
-Doug
 
Hi Doug,

Are you always importing from the same spreadsheet? If
so, you could link it to your database and design an
append query to append all employees that are not already
in the destination table - no coding required (assuming
that the spreadsheet is in a format that can be linked).
You could manually run this query at any time, or you
could have it triggered by clicking a button or as a part
of a macro.

If you want to be able to specify a spreadsheet name as
part of the process (if the name will be changing), you
would probably have to use VBA and there would be a
variety of ways that you could use VBA to append the
data. One idea would be to use VBA to query the user for
the name and establish the link to the spreadsheet using
DoCmd.TransferSpreadsheet (specifying link rather than
import). Then you could create the sql code in VBA to
append employees that do not already exist in the
destination table, or you could just run a pre-designed
query if you always give the same name to the linked
spreadsheet.

Post back if you would like further info on either of the
above methods, or with a little more info on the process
and table/field names if neither of them will work for
you.

HTH, Ted Allen
 
The later idea sounds very interesting. I'm not familar with
DoCmd.TransferSpreadsheet.

The process would then be -

1. Prompt the user for the spreadsheet name, it does change
2. Transfer the spreadsheet - not sure what this does
3. Run SQL

Do you have to create a DAO connection to run SQL against the tables?
 
I did try creating a link to the spreadsheet, but it fails. I use employee
numbers, and they may start with a zero, so the data type needs to be a
string. Access is trying to make it a number or something, most of the data
shows up as #Num!, which is no good. I don't see anyway I can convince
Access the field is a string.
 
Hmm, what is the cell format in Excel? If you format it
as text Access might import it as text. Do you have
control over the spreadsheet properties of the files that
you will be importing? If so, could you make the excel
cells numeric and just use formatting in Excel and Access
to display the leading 0(s) when needed?

As far as running sql in VBA, you can run action queries
using DoCmd.RunSQL without establishing a DAO
connection. I've never worried about any overhead
establishing the DAO connection though because it is
always available by just setting your database object
equal to CurrentDB.

The DoCmD.TransferSpreadsheet just automates the
importing or linking that you can do manually, so if the
manual linking isn't working this method won't work
either.

I would focus on trying to get the spreadsheet to link
properly, because the options after that will probably be
a lot more work (you may have to establish a connection
to the worksheet object and then read cell by cell in
code and determine if the data needs to be added to the
table, then append to the table via a recordset as needed.

I have a little experience with connecting to Excel
objects, but not a lot so I may or may not be able to
help you down that path.

Post back and let me know if you have any luck with
linking to the spreadsheet, and also whether you will be
able to control the format of future spreadsheets.

-Ted Allen
 
I did try changing the cell properties in Excel to text and it still didn't
work. It seems that Access isn't reading the format from excel, but instead
makes it's own decision. Talking to others they have run into the same
problem. Access reads the first few records and makes decisions. The problem
is the data may be different further on.

So, I see no other choice but to code it.

Any good websites you suggest for Access Coding?

Thanks for your help,
Doug
 
Hi Doug,

Well, to be honest I use this newsgroup the most. You
may want to do a new post in the Importing/Exporting
group in Access, or look for a similar one under Excel,
to see what suggestions you receive if nothing in this
message helps solve the problem.

There will likely be many ways that you can approach
this, so if you do a follow-up post it may help to
include some info on what your internal process will be.
For example, it may make a big difference whether you
have control of the excel file to start with rather than
being stuck with whatever you receive from another
group. Other considerations that would be helpful are
things such as how often the file will get filled out,
what you need to do with it, etc.

Usually when I am looking into something like this I will
use Google to search the newsgroups because it works much
better than the search offered by the Microsoft site. If
you haven't done this, you can go to the following link:

http://groups.google.com/groups?hl=en&lr=&ie=UTF-8&oe=UTF-
8&group=microsoft.public.access

In doing a quick search, I found a few interesting prior
posts:

http://groups.google.com/groups?hl=en&lr=&ie=UTF-
8&threadm=ekbRBDtFEHA.3188%
40TK2MSFTNGP09.phx.gbl&rnum=4&prev=/groups%3Fhl%3Den%26lr%
3D%26ie%3DUTF-8%26q%3Dimport%2Bexcel%2Baccess%2Bwrong%
2Bformat%2Bgroup%253Amicrosoft.public.*%26btnG%3DSearch%
26meta%3Dgroup%253Dmicrosoft.public.access.*

http://groups.google.com/groups?hl=en&lr=&ie=UTF-
8&threadm=3912b7d9.2212787%
40news.proaxis.com&rnum=4&prev=/groups%3Fnum%3D20%26hl%
3Den%26lr%3D%26ie%3DUTF-8%26q%3Dexcel%2Baccess%2Bconnect%
2Bcreatetabledef%2Bgroup%253Amicrosoft.public.*%26btnG%
3DSearch

A few ideas for you to consider:

The best way of handling this may be to just enter the
ID's as numbers and leave out the leading zero's. The
leading zero's can be displayed in access by just setting
the format to "0000" where the number of zero's equals
the number of digits in the employee ID. You could also
probably use this format in Excel for the display,
although I'm not sure if that would throw off the import
and/or linking.

As mentioned in one of the posts above, there is
apparently an IMEX setting in the registry that will
determine whether mixed fields are imported as text, or
as the most prevalent type, you may be able to change
yours to text.

There is an alternative method for linking to a database
by connecting to the excel file as a database and using
the createtabledef method to create a table definition
for the worksheet and then appending this to the tables
collection of the Access Database. This may give
different results than the transferdatabase command. I
think that the connection also may allow you to set the
IMEX mode.

You may be able to import your numbers as text by typing
an apostrophe before all of the entries in the problem
field.

You may also be able to work with the Excel file as a CSV
file. When Excel opens CSV files, they look like
standard spreadsheets, but are actually saved in CSV
format. If the CSV format would be useable on the Excel
side, you would have control over the field formatting
when importing to Access (unlike Excel sheets, Access
lets you explicitly specify the field formats for text
files, and you can save the specification after doing it
once for use with the transfertext command).

Similarly, you may be able to use VBA to automate the
export of the Excel data to a text file, and then the
import of the resulting text file.

Finally, I can't remember if you already tried importing
rather than linking, but one of the posts that I saw said
that Excel uses a slightly different method for importing
rather than linking. Also, if you import you may be able
to change the table properties to specify text for the
Employee ID. Similarly, you may be able to just always
use this table as a temp table where you delete records
prior to import, then import as an append to the table,
then query new employee id's to your permanent table.

I would try to look for the easiest approaches first,
before resigning to connecting to the worksheet and
reading the contents cell by cell, but that is an option
if necessary.

Sorry I could't be more help. Hopefully one of the above
ideas may pan out, or, if not, hopefully you can get
further help from the newsgroup.

-Ted Allen
 
...
I did try changing the cell properties in Excel to text and it still didn't
work. It seems that Access isn't reading the format from excel, but instead
makes it's own decision. Talking to others they have run into the same
problem. Access reads the first few records and makes decisions. The problem
is the data may be different further on.
Click to expand...

This is a Jet process, not an MS Access one. See:

http://www.dicks-blog.com/excel/2004/06/external_data_m.html

Jamie.

--
 
Back
Top