Importing

  • Thread starter Thread starter Pegthad
  • Start date Start date
P

Pegthad

I am able to import data into a new table but I am unable
to import it into an existing table with the exact
structure. A general error message appears. Any ideas?
Regards
~P
 
We are importing .xls spreadsheet.
-----Original Message-----
What format is the file from which you are importing it?

I am able to import data into a new table but I am unable
to import it into an existing table with the exact
structure. A general error message appears. Any ideas?
Regards
~P
Click to expand...

--
John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.
.
Click to expand...
 
As far as I can remember, you can only import Excel data into an
existing Access table if the Excel table has "field names" at the top of
each column that exactly match the names of the fields in the existing
table.

We are importing .xls spreadsheet.
-----Original Message-----
What format is the file from which you are importing it?

I am able to import data into a new table but I am unable
to import it into an existing table with the exact
structure. A general error message appears. Any ideas?
Regards
~P
Click to expand...

--
John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.
.
Click to expand...
Click to expand...
 
John Nurick wrote ...
As far as I can remember, you can only import Excel data into an
existing Access table if the Excel table has "field names" at the top of
each column that exactly match the names of the fields in the existing
table.
Click to expand...

Column aliases should get round this problem.

This if the worksheet has different headers:

INSERT INTO
MyTable
(MyCol1, MyCol2)
SELECT
ExcelCol1 AS MyCol1,
ExcelCol2 AS MyCol2
FROM
[Excel 8.0;HDR=Yes;Database=C:\MyWorkbook.xls;].[MySheet$]
;

This if the worksheet has no headers:

INSERT INTO
MyTable
(MyCol1, MyCol2)
SELECT
F1 AS MyCol1,
F1 AS MyCol2
FROM
[Excel 8.0;HDR=No;Database=C:\MyWorkbook.xls;].[MySheet$]
;

Jamie.

--
 
I should have said,

As far I as can remember, the Access spreadsheet import wizard will only
import...

<g>

John Nurick wrote ...
As far as I can remember, you can only import Excel data into an
existing Access table if the Excel table has "field names" at the top of
each column that exactly match the names of the fields in the existing
table.
Click to expand...

Column aliases should get round this problem.

This if the worksheet has different headers:

INSERT INTO
MyTable
(MyCol1, MyCol2)
SELECT
ExcelCol1 AS MyCol1,
ExcelCol2 AS MyCol2
FROM
[Excel 8.0;HDR=Yes;Database=C:\MyWorkbook.xls;].[MySheet$]
;

This if the worksheet has no headers:

INSERT INTO
MyTable
(MyCol1, MyCol2)
SELECT
F1 AS MyCol1,
F1 AS MyCol2
FROM
[Excel 8.0;HDR=No;Database=C:\MyWorkbook.xls;].[MySheet$]
;

Jamie.
Click to expand...
 
John Nurick wrote ...
I should have said,

As far I as can remember, the Access spreadsheet import wizard will only
import...

<g>
Click to expand...

So that's another good reason for not using the Access spreadsheet
import wizard <vbg>.

Jamie.

--
 
So that's another good reason for not using the Access spreadsheet
import wizard <vbg>.
Click to expand...

Few people have your grasp of the Jet SQL syntax: can you point to a
good source of examples?
 
John Nurick wrote ...
Few people have your grasp of the Jet SQL syntax
Click to expand...

I respond well to flattery <g>. Using SQL directly means greater
control so it's worth getting to know. Often, features such as
TransferSpreadsheet are implicitly executing SQL on your behalf and as
a developer I want to be in on the secret. For example,
TransferSpreadsheet where the target is a defined Name ('named range')
in some circumstances issues a DROP TABLE before issuing a
SELECT..INTO query. Maybe it's just me but if a DROP TABLE is
happening, I want to be in charge!
can you point to a good source of examples?
Click to expand...

Articles on MSDN. Admittedly, they are not easy to find because they
have misleading titles e.g. this one's good for 'Excel' SQL...

Microsoft Knowledge Base Article - 257819
How To Use ADO with Excel Data from Visual Basic or VBA
http://support.microsoft.com/default.aspx?scid=kb;en-us;Q257819#RetrieveExcel

....but would be better titled, 'How To Use Excel Data with the Jet
Provider'.

For general Jet SQL (version Jet 4.0), you can't get better than this
article...

Intermediate Microsoft Jet SQL for Access 2000
http://msdn.microsoft.com/library/en-us/dnacc2k/html/acintsql.asp

....but why oh why is 'Access 2000' mentioned in the title? It includes
Jet features that can't even be achieved using the MS Access UI (nor
DAO) e.g. a check constraint.

And if you want to use SQL to do 'fun' things such as creating users
and altering permissions try...

Advanced Microsoft Jet SQL for Access 2000

http://msdn.microsoft.com/library/en-us/dnacc2k/html/acadvsql.asp

....but again why the title? Surely one would only want to do this kind
of thing if they *didn't* have the MS Access app?!

Jamie.

--
 
Back
Top