Access Data without Importing or Linking

[Brad]

Thanks for taking the time to read my question.

I have a DB that I am currently designing. I will have some data in Excel
that I want to import into a table. The Excel file will contain ALL the
companies available. I only want to import the NEW companies (The ones that
are in the Excel file but not in the DB).

I can do this if I import the Excel to a Temp table, but I am wondering if I
can just open Excel, access the data, run a query or something to find the
new records, import the new records without importing all the data from
Excel. Access will grow too fast and will have to be compacted too often.
My users will not remember to do that.

Does Linking keep the DB size down?

Thanks for the help.

Brad

 

[Klatuu]

I would recommend linking and writing an append query to add the new companies.
In my code or marco, I would create the link, do the query, delete the link.
As far as compacting, why not just use the compact on close option in
Tools->Options - General Tab. This causes a compact every time the user
closes the database. That way, they can't forget.

 

[Brad]

Compact on close.... never knew that was there!

Perfect.

Thanks,

Brad

 

[John Nurick]

Hi Brad,

You can use an Excel sheet as the source of a SQL append query, e.g.

INSERT INTO TheTable
SELECT F1, F2, F3, F4, F5
FROM [Excel 8.0;HDR=No;database=C:\MyWorkbook.xls;].[Sheet1$A1:E100]
;

As well as a range of cells, the last part of the FROM clause can
specify "all the data on a sheet" ([Sheet1$]) or a named range
([RangeName]). The HDR= argument says whether or not the first row of
the range contains field names.

You can add a WHERE clause or join to limit the records that are
imported, e.g.

INSERT INTO TheTable
SELECT ID, FirstName, LastName
FROM [Excel 8.0;HDR=Yes;database=C:\MyWorkbook.xls;].[Sheet2]
WHERE ID NOT IN (SELECT ID FROM TheTable)
;


(or just rely on your primary key index and other constraints to
prevent import of records that are already in the table).

 

[Klatuu]

John,

Extra Thanks for this post! I was totally unaware this could be done, but
it is something that will save me a bunch of time and work. We do a lot of
slushing data back and forth between Access and Excel.

Hi Brad,

You can use an Excel sheet as the source of a SQL append query, e.g.

INSERT INTO TheTable
SELECT F1, F2, F3, F4, F5
FROM [Excel 8.0;HDR=No;database=C:\MyWorkbook.xls;].[Sheet1$A1:E100]
;

As well as a range of cells, the last part of the FROM clause can
specify "all the data on a sheet" ([Sheet1$]) or a named range
([RangeName]). The HDR= argument says whether or not the first row of
the range contains field names.

You can add a WHERE clause or join to limit the records that are
imported, e.g.

INSERT INTO TheTable
SELECT ID, FirstName, LastName
FROM [Excel 8.0;HDR=Yes;database=C:\MyWorkbook.xls;].[Sheet2]
WHERE ID NOT IN (SELECT ID FROM TheTable)
;


(or just rely on your primary key index and other constraints to
prevent import of records that are already in the table).

Thanks for taking the time to read my question.

I have a DB that I am currently designing. I will have some data in Excel
that I want to import into a table. The Excel file will contain ALL the
companies available. I only want to import the NEW companies (The ones that
are in the Excel file but not in the DB).

I can do this if I import the Excel to a Temp table, but I am wondering if I
can just open Excel, access the data, run a query or something to find the
new records, import the new records without importing all the data from
Excel. Access will grow too fast and will have to be compacted too often.
My users will not remember to do that.

Does Linking keep the DB size down?

Thanks for the help.

Brad

 

[Klatuu]

John,

I tried using the example you posted eariler, but I am getting run time
error 3005 "Not a Valid File Name" error. The file does exist. I had some
other issues, but managed to get through them. If you would please look at
my code and give me any ideas on what might be the problem here.

Notice I changed the ; to , from your example. With the ; the error was run
time 3170 "Could not find installable ISAM"

Sub ImportPipeline()
Dim strSQL As String
strSQL = "INSERT INTO tblPipeline80 ( Curr_Year, Curr_Month, " _
& "Program_Manager, ITM, Opportunity, Resource, Jan, Feb, Mar, " _
& "Apr, May, Jun, Jul, Aug, Sep, Oct, Nov, Dec ) " _
& "SELECT '2005' as Curr_Year, '04' as Curr_Month, A4, B4, C4, D4,
E4, " _
& "F4, G4, H4, I4, J4, K4, L4, M4, N4, O4, P4 " _
& "FROM [Excel 9.0,HDR=No,database=C:\Documents and
Settings\hargida\My Documents\Access\April.xls,]" _
& ".[80%$A4100];"
CurrentDb.Execute strSQL
End Sub

Hi Brad,

You can use an Excel sheet as the source of a SQL append query, e.g.

INSERT INTO TheTable
SELECT F1, F2, F3, F4, F5
FROM [Excel 8.0;HDR=No;database=C:\MyWorkbook.xls;].[Sheet1$A1:E100]
;

As well as a range of cells, the last part of the FROM clause can
specify "all the data on a sheet" ([Sheet1$]) or a named range
([RangeName]). The HDR= argument says whether or not the first row of
the range contains field names.

You can add a WHERE clause or join to limit the records that are
imported, e.g.

INSERT INTO TheTable
SELECT ID, FirstName, LastName
FROM [Excel 8.0;HDR=Yes;database=C:\MyWorkbook.xls;].[Sheet2]
WHERE ID NOT IN (SELECT ID FROM TheTable)
;


(or just rely on your primary key index and other constraints to
prevent import of records that are already in the table).

Thanks for taking the time to read my question.

I have a DB that I am currently designing. I will have some data in Excel
that I want to import into a table. The Excel file will contain ALL the
companies available. I only want to import the NEW companies (The ones that
are in the Excel file but not in the DB).

I can do this if I import the Excel to a Temp table, but I am wondering if I
can just open Excel, access the data, run a query or something to find the
new records, import the new records without importing all the data from
Excel. Access will grow too fast and will have to be compacted too often.
My users will not remember to do that.

Does Linking keep the DB size down?

Thanks for the help.

Brad

 

[John Nurick]

Your query is specifying field names A4, B4..., but you have specified
HDR=No.

With HDR=No, Jet uses the default field names F1, F2...
With HDR=Yes, the field names in the query must match the contents of
the cells in the first row.

I'm not surprised the , gives a "Not a valid file name error". I reckon
that Jet is reading the filename as "April.xls,", and "xls," is not a
registered extension for the Excel ISAM driver.

John,

I tried using the example you posted eariler, but I am getting run time
error 3005 "Not a Valid File Name" error. The file does exist. I had some
other issues, but managed to get through them. If you would please look at
my code and give me any ideas on what might be the problem here.

Notice I changed the ; to , from your example. With the ; the error was run
time 3170 "Could not find installable ISAM"

Sub ImportPipeline()
Dim strSQL As String
strSQL = "INSERT INTO tblPipeline80 ( Curr_Year, Curr_Month, " _
& "Program_Manager, ITM, Opportunity, Resource, Jan, Feb, Mar, " _
& "Apr, May, Jun, Jul, Aug, Sep, Oct, Nov, Dec ) " _
& "SELECT '2005' as Curr_Year, '04' as Curr_Month, A4, B4, C4, D4,
E4, " _
& "F4, G4, H4, I4, J4, K4, L4, M4, N4, O4, P4 " _
& "FROM [Excel 9.0,HDR=No,database=C:\Documents and
Settings\hargida\My Documents\Access\April.xls,]" _
& ".[80%$A4100];"
CurrentDb.Execute strSQL
End Sub

Hi Brad,

You can use an Excel sheet as the source of a SQL append query, e.g.

INSERT INTO TheTable
SELECT F1, F2, F3, F4, F5
FROM [Excel 8.0;HDR=No;database=C:\MyWorkbook.xls;].[Sheet1$A1:E100]
;

As well as a range of cells, the last part of the FROM clause can
specify "all the data on a sheet" ([Sheet1$]) or a named range
([RangeName]). The HDR= argument says whether or not the first row of
the range contains field names.

You can add a WHERE clause or join to limit the records that are
imported, e.g.

INSERT INTO TheTable
SELECT ID, FirstName, LastName
FROM [Excel 8.0;HDR=Yes;database=C:\MyWorkbook.xls;].[Sheet2]
WHERE ID NOT IN (SELECT ID FROM TheTable)
;


(or just rely on your primary key index and other constraints to
prevent import of records that are already in the table).

Thanks for taking the time to read my question.

I have a DB that I am currently designing. I will have some data in Excel
that I want to import into a table. The Excel file will contain ALL the
companies available. I only want to import the NEW companies (The ones that
are in the Excel file but not in the DB).

I can do this if I import the Excel to a Temp table, but I am wondering if I
can just open Excel, access the data, run a query or something to find the
new records, import the new records without importing all the data from
Excel. Access will grow too fast and will have to be compacted too often.
My users will not remember to do that.

Does Linking keep the DB size down?

Thanks for the help.

Brad

 

[Klatuu]

Thansk, John. I understand the field naming problems. When I looked at your
example, F1, F2... I confused that as a field name with Excel Cell
designation. Now, the file name problem I don't quite get. why would .xls,
be seen differently than .xls; by Jet? If I use ; as in your example, I get
the message I mentioned earlier. I will give this a try this morning. And,
again, thanks for your help.

Your query is specifying field names A4, B4..., but you have specified
HDR=No.

With HDR=No, Jet uses the default field names F1, F2...
With HDR=Yes, the field names in the query must match the contents of
the cells in the first row.

I'm not surprised the , gives a "Not a valid file name error". I reckon
that Jet is reading the filename as "April.xls,", and "xls," is not a
registered extension for the Excel ISAM driver.

John,

I tried using the example you posted eariler, but I am getting run time
error 3005 "Not a Valid File Name" error. The file does exist. I had some
other issues, but managed to get through them. If you would please look at
my code and give me any ideas on what might be the problem here.

Notice I changed the ; to , from your example. With the ; the error was run
time 3170 "Could not find installable ISAM"

Sub ImportPipeline()
Dim strSQL As String
strSQL = "INSERT INTO tblPipeline80 ( Curr_Year, Curr_Month, " _
& "Program_Manager, ITM, Opportunity, Resource, Jan, Feb, Mar, " _
& "Apr, May, Jun, Jul, Aug, Sep, Oct, Nov, Dec ) " _
& "SELECT '2005' as Curr_Year, '04' as Curr_Month, A4, B4, C4, D4,
E4, " _
& "F4, G4, H4, I4, J4, K4, L4, M4, N4, O4, P4 " _
& "FROM [Excel 9.0,HDR=No,database=C:\Documents and
Settings\hargida\My Documents\Access\April.xls,]" _
& ".[80%$A4100];"
CurrentDb.Execute strSQL
End Sub

Hi Brad,

You can use an Excel sheet as the source of a SQL append query, e.g.

INSERT INTO TheTable
SELECT F1, F2, F3, F4, F5
FROM [Excel 8.0;HDR=No;database=C:\MyWorkbook.xls;].[Sheet1$A1:E100]
;

As well as a range of cells, the last part of the FROM clause can
specify "all the data on a sheet" ([Sheet1$]) or a named range
([RangeName]). The HDR= argument says whether or not the first row of
the range contains field names.

You can add a WHERE clause or join to limit the records that are
imported, e.g.

INSERT INTO TheTable
SELECT ID, FirstName, LastName
FROM [Excel 8.0;HDR=Yes;database=C:\MyWorkbook.xls;].[Sheet2]
WHERE ID NOT IN (SELECT ID FROM TheTable)
;


(or just rely on your primary key index and other constraints to
prevent import of records that are already in the table).

On Tue, 10 May 2005 12:50:06 -0700, "Brad"

Thanks for taking the time to read my question.

I have a DB that I am currently designing. I will have some data in Excel
that I want to import into a table. The Excel file will contain ALL the
companies available. I only want to import the NEW companies (The ones that
are in the Excel file but not in the DB).

I can do this if I import the Excel to a Temp table, but I am wondering if I
can just open Excel, access the data, run a query or something to find the
new records, import the new records without importing all the data from
Excel. Access will grow too fast and will have to be compacted too often.
My users will not remember to do that.

Does Linking keep the DB size down?

Thanks for the help.

Brad

 

[John Nurick]

The sample FROM clause I posted was

FROM [Excel 8.0;HDR=No;database=C:\MyWorkbook.xls;].[Sheet1$A1:E100]

You have

FROM [Excel 9.0,HDR=No,database=C:\Documents and Settings\hargida\My Documents\Access\April.xls,]

Even in Office 2003 (i.e. version 11 of the Office apps) the Excel ISAM
requires "Excel 8.0" (or earlier). Using "Excel 9.0" gives you the
"Cannot find installable ISAM" error. As for using commas instead of
semicolons...

Thansk, John. I understand the field naming problems. When I looked at your
example, F1, F2... I confused that as a field name with Excel Cell
designation. Now, the file name problem I don't quite get. why would .xls,
be seen differently than .xls; by Jet? If I use ; as in your example, I get
the message I mentioned earlier. I will give this a try this morning. And,
again, thanks for your help.

Your query is specifying field names A4, B4..., but you have specified
HDR=No.

With HDR=No, Jet uses the default field names F1, F2...
With HDR=Yes, the field names in the query must match the contents of
the cells in the first row.

I'm not surprised the , gives a "Not a valid file name error". I reckon
that Jet is reading the filename as "April.xls,", and "xls," is not a
registered extension for the Excel ISAM driver.

John,

I tried using the example you posted eariler, but I am getting run time
error 3005 "Not a Valid File Name" error. The file does exist. I had some
other issues, but managed to get through them. If you would please look at
my code and give me any ideas on what might be the problem here.

Notice I changed the ; to , from your example. With the ; the error was run
time 3170 "Could not find installable ISAM"

Sub ImportPipeline()
Dim strSQL As String
strSQL = "INSERT INTO tblPipeline80 ( Curr_Year, Curr_Month, " _
& "Program_Manager, ITM, Opportunity, Resource, Jan, Feb, Mar, " _
& "Apr, May, Jun, Jul, Aug, Sep, Oct, Nov, Dec ) " _
& "SELECT '2005' as Curr_Year, '04' as Curr_Month, A4, B4, C4, D4,
E4, " _
& "F4, G4, H4, I4, J4, K4, L4, M4, N4, O4, P4 " _
& "FROM [Excel 9.0,HDR=No,database=C:\Documents and
Settings\hargida\My Documents\Access\April.xls,]" _
& ".[80%$A4100];"
CurrentDb.Execute strSQL
End Sub


:

Hi Brad,

You can use an Excel sheet as the source of a SQL append query, e.g.

INSERT INTO TheTable
SELECT F1, F2, F3, F4, F5
FROM [Excel 8.0;HDR=No;database=C:\MyWorkbook.xls;].[Sheet1$A1:E100]
;

As well as a range of cells, the last part of the FROM clause can
specify "all the data on a sheet" ([Sheet1$]) or a named range
([RangeName]). The HDR= argument says whether or not the first row of
the range contains field names.

You can add a WHERE clause or join to limit the records that are
imported, e.g.

INSERT INTO TheTable
SELECT ID, FirstName, LastName
FROM [Excel 8.0;HDR=Yes;database=C:\MyWorkbook.xls;].[Sheet2]
WHERE ID NOT IN (SELECT ID FROM TheTable)
;


(or just rely on your primary key index and other constraints to
prevent import of records that are already in the table).

On Tue, 10 May 2005 12:50:06 -0700, "Brad"

Thanks for taking the time to read my question.

I have a DB that I am currently designing. I will have some data in Excel
that I want to import into a table. The Excel file will contain ALL the
companies available. I only want to import the NEW companies (The ones that
are in the Excel file but not in the DB).

I can do this if I import the Excel to a Temp table, but I am wondering if I
can just open Excel, access the data, run a query or something to find the
new records, import the new records without importing all the data from
Excel. Access will grow too fast and will have to be compacted too often.
My users will not remember to do that.

Does Linking keep the DB size down?

Thanks for the help.

Brad

 

[Klatuu]

Thanks for responding, John. I have found some documentation on the subject
and I think I can work it out from here. I did see that Excel 8.0 is correct
even for newer verisions. I found some inf in Access 2002 Desktop
Developer's Handbood by Paul Litwin, Ken Getz, and Mike Gunderloy. I
acquired the full set with the Enterpise edition in a used bookstore for
$20.00. Great reference and excellent how to.

The sample FROM clause I posted was

FROM [Excel 8.0;HDR=No;database=C:\MyWorkbook.xls;].[Sheet1$A1:E100]

You have

FROM [Excel 9.0,HDR=No,database=C:\Documents and Settings\hargida\My Documents\Access\April.xls,]

Even in Office 2003 (i.e. version 11 of the Office apps) the Excel ISAM
requires "Excel 8.0" (or earlier). Using "Excel 9.0" gives you the
"Cannot find installable ISAM" error. As for using commas instead of
semicolons...

Thansk, John. I understand the field naming problems. When I looked at your
example, F1, F2... I confused that as a field name with Excel Cell
designation. Now, the file name problem I don't quite get. why would .xls,
be seen differently than .xls; by Jet? If I use ; as in your example, I get
the message I mentioned earlier. I will give this a try this morning. And,
again, thanks for your help.

Your query is specifying field names A4, B4..., but you have specified
HDR=No.

With HDR=No, Jet uses the default field names F1, F2...
With HDR=Yes, the field names in the query must match the contents of
the cells in the first row.

I'm not surprised the , gives a "Not a valid file name error". I reckon
that Jet is reading the filename as "April.xls,", and "xls," is not a
registered extension for the Excel ISAM driver.

On Wed, 11 May 2005 14:41:03 -0700, Klatuu

John,

I tried using the example you posted eariler, but I am getting run time
error 3005 "Not a Valid File Name" error. The file does exist. I had some
other issues, but managed to get through them. If you would please look at
my code and give me any ideas on what might be the problem here.

Notice I changed the ; to , from your example. With the ; the error was run
time 3170 "Could not find installable ISAM"

Sub ImportPipeline()
Dim strSQL As String
strSQL = "INSERT INTO tblPipeline80 ( Curr_Year, Curr_Month, " _
& "Program_Manager, ITM, Opportunity, Resource, Jan, Feb, Mar, " _
& "Apr, May, Jun, Jul, Aug, Sep, Oct, Nov, Dec ) " _
& "SELECT '2005' as Curr_Year, '04' as Curr_Month, A4, B4, C4, D4,
E4, " _
& "F4, G4, H4, I4, J4, K4, L4, M4, N4, O4, P4 " _
& "FROM [Excel 9.0,HDR=No,database=C:\Documents and
Settings\hargida\My Documents\Access\April.xls,]" _
& ".[80%$A4100];"
CurrentDb.Execute strSQL
End Sub


:

Hi Brad,

You can use an Excel sheet as the source of a SQL append query, e.g.

INSERT INTO TheTable
SELECT F1, F2, F3, F4, F5
FROM [Excel 8.0;HDR=No;database=C:\MyWorkbook.xls;].[Sheet1$A1:E100]
;

As well as a range of cells, the last part of the FROM clause can
specify "all the data on a sheet" ([Sheet1$]) or a named range
([RangeName]). The HDR= argument says whether or not the first row of
the range contains field names.

You can add a WHERE clause or join to limit the records that are
imported, e.g.

INSERT INTO TheTable
SELECT ID, FirstName, LastName
FROM [Excel 8.0;HDR=Yes;database=C:\MyWorkbook.xls;].[Sheet2]
WHERE ID NOT IN (SELECT ID FROM TheTable)
;


(or just rely on your primary key index and other constraints to
prevent import of records that are already in the table).

On Tue, 10 May 2005 12:50:06 -0700, "Brad"

Thanks for taking the time to read my question.

I have a DB that I am currently designing. I will have some data in Excel
that I want to import into a table. The Excel file will contain ALL the
companies available. I only want to import the NEW companies (The ones that
are in the Excel file but not in the DB).

I can do this if I import the Excel to a Temp table, but I am wondering if I
can just open Excel, access the data, run a query or something to find the
new records, import the new records without importing all the data from
Excel. Access will grow too fast and will have to be compacted too often.
My users will not remember to do that.

Does Linking keep the DB size down?

Thanks for the help.

Brad

 

[John Nurick]

$20 is excellent value. I wish I had luck like that in bookshops<g>.

Thanks for responding, John. I have found some documentation on the subject
and I think I can work it out from here. I did see that Excel 8.0 is correct
even for newer verisions. I found some inf in Access 2002 Desktop
Developer's Handbood by Paul Litwin, Ken Getz, and Mike Gunderloy. I
acquired the full set with the Enterpise edition in a used bookstore for
$20.00. Great reference and excellent how to.

The sample FROM clause I posted was

FROM [Excel 8.0;HDR=No;database=C:\MyWorkbook.xls;].[Sheet1$A1:E100]

You have

FROM [Excel 9.0,HDR=No,database=C:\Documents and Settings\hargida\My Documents\Access\April.xls,]

Even in Office 2003 (i.e. version 11 of the Office apps) the Excel ISAM
requires "Excel 8.0" (or earlier). Using "Excel 9.0" gives you the
"Cannot find installable ISAM" error. As for using commas instead of
semicolons...

Thansk, John. I understand the field naming problems. When I looked at your
example, F1, F2... I confused that as a field name with Excel Cell
designation. Now, the file name problem I don't quite get. why would .xls,
be seen differently than .xls; by Jet? If I use ; as in your example, I get
the message I mentioned earlier. I will give this a try this morning. And,
again, thanks for your help.

:

Your query is specifying field names A4, B4..., but you have specified
HDR=No.

With HDR=No, Jet uses the default field names F1, F2...
With HDR=Yes, the field names in the query must match the contents of
the cells in the first row.

I'm not surprised the , gives a "Not a valid file name error". I reckon
that Jet is reading the filename as "April.xls,", and "xls," is not a
registered extension for the Excel ISAM driver.

On Wed, 11 May 2005 14:41:03 -0700, Klatuu

John,

I tried using the example you posted eariler, but I am getting run time
error 3005 "Not a Valid File Name" error. The file does exist. I had some
other issues, but managed to get through them. If you would please look at
my code and give me any ideas on what might be the problem here.

Notice I changed the ; to , from your example. With the ; the error was run
time 3170 "Could not find installable ISAM"

Sub ImportPipeline()
Dim strSQL As String
strSQL = "INSERT INTO tblPipeline80 ( Curr_Year, Curr_Month, " _
& "Program_Manager, ITM, Opportunity, Resource, Jan, Feb, Mar, " _
& "Apr, May, Jun, Jul, Aug, Sep, Oct, Nov, Dec ) " _
& "SELECT '2005' as Curr_Year, '04' as Curr_Month, A4, B4, C4, D4,
E4, " _
& "F4, G4, H4, I4, J4, K4, L4, M4, N4, O4, P4 " _
& "FROM [Excel 9.0,HDR=No,database=C:\Documents and
Settings\hargida\My Documents\Access\April.xls,]" _
& ".[80%$A4100];"
CurrentDb.Execute strSQL
End Sub


:

Hi Brad,

You can use an Excel sheet as the source of a SQL append query, e.g.

INSERT INTO TheTable
SELECT F1, F2, F3, F4, F5
FROM [Excel 8.0;HDR=No;database=C:\MyWorkbook.xls;].[Sheet1$A1:E100]
;

As well as a range of cells, the last part of the FROM clause can
specify "all the data on a sheet" ([Sheet1$]) or a named range
([RangeName]). The HDR= argument says whether or not the first row of
the range contains field names.

You can add a WHERE clause or join to limit the records that are
imported, e.g.

INSERT INTO TheTable
SELECT ID, FirstName, LastName
FROM [Excel 8.0;HDR=Yes;database=C:\MyWorkbook.xls;].[Sheet2]
WHERE ID NOT IN (SELECT ID FROM TheTable)
;


(or just rely on your primary key index and other constraints to
prevent import of records that are already in the table).

On Tue, 10 May 2005 12:50:06 -0700, "Brad"

Thanks for taking the time to read my question.

I have a DB that I am currently designing. I will have some data in Excel
that I want to import into a table. The Excel file will contain ALL the
companies available. I only want to import the NEW companies (The ones that
are in the Excel file but not in the DB).

I can do this if I import the Excel to a Temp table, but I am wondering if I
can just open Excel, access the data, run a query or something to find the
new records, import the new records without importing all the data from
Excel. Access will grow too fast and will have to be compacted too often.
My users will not remember to do that.

Does Linking keep the DB size down?

Thanks for the help.

Brad

 

[Klatuu]

John,

Just to let you know, I got it working! If not talented, I am, at least,
tenacious. I really do appreciate your help. There are, of course other
ways I could do this, but I am trying to expand my skills so I can take my
company's (the worst you ever saw) A2K toward A2003 using ADO, and eventuall
to an SQL database. This, to me was a giant step.

Sub ImportPipeline()
Dim strSQL As String
strSQL = "INSERT INTO tblPipeline80 (Curr_Year, Curr_Month,
Program_Manager, ITM, " _
& "Opportunity, Resource, Jan, Feb, Mar, Apr, May, Jun, " _
& "Jul, Aug, Sep, Oct, Nov, Dec, Source_Sheet ) " _
& "SELECT '2005', '04', [Prog Mgr], ITM, Opportunity, Resource, Jan,
Feb, Mar, Apr, May, " _
& "Jun, Jul, Aug, Sep, Oct, Nov, Dec, 'Pipeline 2005 April'" _
& "FROM [Excel 8.0;Hdr=Yes;DATABASE=" _
& "C:\Documents and Settings\hargida\My Documents\Access\" _
& "Pipeline 2005 April.xls;].Pipeline;"
CurrentDb.Execute strSQL
End Sub

$20 is excellent value. I wish I had luck like that in bookshops<g>.

Thanks for responding, John. I have found some documentation on the subject
and I think I can work it out from here. I did see that Excel 8.0 is correct
even for newer verisions. I found some inf in Access 2002 Desktop
Developer's Handbood by Paul Litwin, Ken Getz, and Mike Gunderloy. I
acquired the full set with the Enterpise edition in a used bookstore for
$20.00. Great reference and excellent how to.

The sample FROM clause I posted was

FROM [Excel 8.0;HDR=No;database=C:\MyWorkbook.xls;].[Sheet1$A1:E100]

You have
FROM [Excel 9.0,HDR=No,database=C:\Documents and Settings\hargida\My Documents\Access\April.xls,]

Even in Office 2003 (i.e. version 11 of the Office apps) the Excel ISAM
requires "Excel 8.0" (or earlier). Using "Excel 9.0" gives you the
"Cannot find installable ISAM" error. As for using commas instead of
semicolons...

On Thu, 12 May 2005 06:24:04 -0700, Klatuu

Thansk, John. I understand the field naming problems. When I looked at your
example, F1, F2... I confused that as a field name with Excel Cell
designation. Now, the file name problem I don't quite get. why would .xls,
be seen differently than .xls; by Jet? If I use ; as in your example, I get
the message I mentioned earlier. I will give this a try this morning. And,
again, thanks for your help.

:

Your query is specifying field names A4, B4..., but you have specified
HDR=No.

With HDR=No, Jet uses the default field names F1, F2...
With HDR=Yes, the field names in the query must match the contents of
the cells in the first row.

I'm not surprised the , gives a "Not a valid file name error". I reckon
that Jet is reading the filename as "April.xls,", and "xls," is not a
registered extension for the Excel ISAM driver.

On Wed, 11 May 2005 14:41:03 -0700, Klatuu

John,

I tried using the example you posted eariler, but I am getting run time
error 3005 "Not a Valid File Name" error. The file does exist. I had some
other issues, but managed to get through them. If you would please look at
my code and give me any ideas on what might be the problem here.

Notice I changed the ; to , from your example. With the ; the error was run
time 3170 "Could not find installable ISAM"

Sub ImportPipeline()
Dim strSQL As String
strSQL = "INSERT INTO tblPipeline80 ( Curr_Year, Curr_Month, " _
& "Program_Manager, ITM, Opportunity, Resource, Jan, Feb, Mar, " _
& "Apr, May, Jun, Jul, Aug, Sep, Oct, Nov, Dec ) " _
& "SELECT '2005' as Curr_Year, '04' as Curr_Month, A4, B4, C4, D4,
E4, " _
& "F4, G4, H4, I4, J4, K4, L4, M4, N4, O4, P4 " _
& "FROM [Excel 9.0,HDR=No,database=C:\Documents and
Settings\hargida\My Documents\Access\April.xls,]" _
& ".[80%$A4100];"
CurrentDb.Execute strSQL
End Sub


:

Hi Brad,

You can use an Excel sheet as the source of a SQL append query, e.g.

INSERT INTO TheTable
SELECT F1, F2, F3, F4, F5
FROM [Excel 8.0;HDR=No;database=C:\MyWorkbook.xls;].[Sheet1$A1:E100]
;

As well as a range of cells, the last part of the FROM clause can
specify "all the data on a sheet" ([Sheet1$]) or a named range
([RangeName]). The HDR= argument says whether or not the first row of
the range contains field names.

You can add a WHERE clause or join to limit the records that are
imported, e.g.

INSERT INTO TheTable
SELECT ID, FirstName, LastName
FROM [Excel 8.0;HDR=Yes;database=C:\MyWorkbook.xls;].[Sheet2]
WHERE ID NOT IN (SELECT ID FROM TheTable)
;


(or just rely on your primary key index and other constraints to
prevent import of records that are already in the table).

On Tue, 10 May 2005 12:50:06 -0700, "Brad"

Thanks for taking the time to read my question.

I have a DB that I am currently designing. I will have some data in Excel
that I want to import into a table. The Excel file will contain ALL the
companies available. I only want to import the NEW companies (The ones that
are in the Excel file but not in the DB).

I can do this if I import the Excel to a Temp table, but I am wondering if I
can just open Excel, access the data, run a query or something to find the
new records, import the new records without importing all the data from
Excel. Access will grow too fast and will have to be compacted too often.
My users will not remember to do that.

Does Linking keep the DB size down?

Thanks for the help.

Brad

 

[sunqiuxia]

fdlkjlkjsdk;ldf

 

[Cydney]

Hi John,
I found this post and gave it a try on my database and I'm getting an error.
It says, "Syntax error in query. Incomplete query clause."

Here's my code:
DoCmd.RunSQL "INSERT INTO [EACXLS-Raw] SELECT F1, F2, F3, F4, F5, F6, F7,
F8, F9, F10, F11, F12, F11, F13, F14 FROM [Excel
8.0;HDR=No;database=MyImportedFile].([Clean$]);"

MyImportedFile is the path and file name of the excel file (received through
a Select File inputbox).

Can you tell me what "incomplete" about it?
--
THX cs

Hi Brad,

You can use an Excel sheet as the source of a SQL append query, e.g.

INSERT INTO TheTable
SELECT F1, F2, F3, F4, F5
FROM [Excel 8.0;HDR=No;database=C:\MyWorkbook.xls;].[Sheet1$A1:E100]
;

As well as a range of cells, the last part of the FROM clause can
specify "all the data on a sheet" ([Sheet1$]) or a named range
([RangeName]). The HDR= argument says whether or not the first row of
the range contains field names.

You can add a WHERE clause or join to limit the records that are
imported, e.g.

INSERT INTO TheTable
SELECT ID, FirstName, LastName
FROM [Excel 8.0;HDR=Yes;database=C:\MyWorkbook.xls;].[Sheet2]
WHERE ID NOT IN (SELECT ID FROM TheTable)
;


(or just rely on your primary key index and other constraints to
prevent import of records that are already in the table).

Thanks for taking the time to read my question.

I have a DB that I am currently designing. I will have some data in Excel
that I want to import into a table. The Excel file will contain ALL the
companies available. I only want to import the NEW companies (The ones that
are in the Excel file but not in the DB).

I can do this if I import the Excel to a Temp table, but I am wondering if I
can just open Excel, access the data, run a query or something to find the
new records, import the new records without importing all the data from
Excel. Access will grow too fast and will have to be compacted too often.
My users will not remember to do that.

Does Linking keep the DB size down?

Thanks for the help.

Brad

 

[Cydney]

Yes, I noticed that too. But it still didn't make it any happier when I put
it in.
--
THX cs

One possibility: the placement of the last semicolon is different (after
file name vs. after sheet/range name).

HTH,
--
George Nicholson

Remove 'Junk' from return address.

Hi John,
I found this post and gave it a try on my database and I'm getting an
error.
It says, "Syntax error in query. Incomplete query clause."

Here's my code:
DoCmd.RunSQL "INSERT INTO [EACXLS-Raw] SELECT F1, F2, F3, F4, F5, F6, F7,
F8, F9, F10, F11, F12, F11, F13, F14 FROM [Excel
8.0;HDR=No;database=MyImportedFile].([Clean$]);"

MyImportedFile is the path and file name of the excel file (received
through
a Select File inputbox).

Can you tell me what "incomplete" about it?
--
THX cs

Hi Brad,

You can use an Excel sheet as the source of a SQL append query, e.g.

INSERT INTO TheTable
SELECT F1, F2, F3, F4, F5
FROM [Excel 8.0;HDR=No;database=C:\MyWorkbook.xls;].[Sheet1$A1:E100]
;

As well as a range of cells, the last part of the FROM clause can
specify "all the data on a sheet" ([Sheet1$]) or a named range
([RangeName]). The HDR= argument says whether or not the first row of
the range contains field names.

You can add a WHERE clause or join to limit the records that are
imported, e.g.

INSERT INTO TheTable
SELECT ID, FirstName, LastName
FROM [Excel 8.0;HDR=Yes;database=C:\MyWorkbook.xls;].[Sheet2]
WHERE ID NOT IN (SELECT ID FROM TheTable)
;


(or just rely on your primary key index and other constraints to
prevent import of records that are already in the table).

On Tue, 10 May 2005 12:50:06 -0700, "Brad"

Thanks for taking the time to read my question.

I have a DB that I am currently designing. I will have some data in
Excel
that I want to import into a table. The Excel file will contain ALL the
companies available. I only want to import the NEW companies (The ones
that
are in the Excel file but not in the DB).

I can do this if I import the Excel to a Temp table, but I am wondering
if I
can just open Excel, access the data, run a query or something to find
the
new records, import the new records without importing all the data from
Excel. Access will grow too fast and will have to be compacted too
often.
My users will not remember to do that.

Does Linking keep the DB size down?

Thanks for the help.

Brad

 

[George Nicholson]

One possibility: the placement of the last semicolon is different (after
file name vs. after sheet/range name).

HTH,
--
George Nicholson

Remove 'Junk' from return address.

Hi John,
I found this post and gave it a try on my database and I'm getting an
error.
It says, "Syntax error in query. Incomplete query clause."

Here's my code:
DoCmd.RunSQL "INSERT INTO [EACXLS-Raw] SELECT F1, F2, F3, F4, F5, F6, F7,
F8, F9, F10, F11, F12, F11, F13, F14 FROM [Excel
8.0;HDR=No;database=MyImportedFile].([Clean$]);"

MyImportedFile is the path and file name of the excel file (received
through
a Select File inputbox).

Can you tell me what "incomplete" about it?
--
THX cs

Hi Brad,

You can use an Excel sheet as the source of a SQL append query, e.g.

INSERT INTO TheTable
SELECT F1, F2, F3, F4, F5
FROM [Excel 8.0;HDR=No;database=C:\MyWorkbook.xls;].[Sheet1$A1:E100]
;

As well as a range of cells, the last part of the FROM clause can
specify "all the data on a sheet" ([Sheet1$]) or a named range
([RangeName]). The HDR= argument says whether or not the first row of
the range contains field names.

You can add a WHERE clause or join to limit the records that are
imported, e.g.

INSERT INTO TheTable
SELECT ID, FirstName, LastName
FROM [Excel 8.0;HDR=Yes;database=C:\MyWorkbook.xls;].[Sheet2]
WHERE ID NOT IN (SELECT ID FROM TheTable)
;


(or just rely on your primary key index and other constraints to
prevent import of records that are already in the table).

Thanks for taking the time to read my question.

I have a DB that I am currently designing. I will have some data in
Excel
that I want to import into a table. The Excel file will contain ALL the
companies available. I only want to import the NEW companies (The ones
that
are in the Excel file but not in the DB).

I can do this if I import the Excel to a Temp table, but I am wondering
if I
can just open Excel, access the data, run a query or something to find
the
new records, import the new records without importing all the data from
Excel. Access will grow too fast and will have to be compacted too
often.
My users will not remember to do that.

Does Linking keep the DB size down?

Thanks for the help.

Brad

 

[John Nurick]

Hi Cydney,

I can see two problems. First, you need to put the actual path and
filename into the [Excel 8.0....;] , not the name of the variable
containing it. Second, the sheet name should be in square brackets but
not parentheses.

Something like

Dim strSQL As String
strSQL = "INSERT INTO [EACXLS-Raw] " _
& "SELECT F1, F2, F3, F4, F5, F6, F7, " _
& "F8, F9, F10, F11, F12, F11, F13, F14 " _
& "FROM [Excel 8.0;HDR=No;database=" _
& MyImportedFile & ";].[Clean$];"
CurrentDb.Execute strSQL, dbFailOnError

Hi John,
I found this post and gave it a try on my database and I'm getting an error.
It says, "Syntax error in query. Incomplete query clause."

Here's my code:
DoCmd.RunSQL "INSERT INTO [EACXLS-Raw] SELECT F1, F2, F3, F4, F5, F6, F7,
F8, F9, F10, F11, F12, F11, F13, F14 FROM [Excel
8.0;HDR=No;database=MyImportedFile].([Clean$]);"

MyImportedFile is the path and file name of the excel file (received through
a Select File inputbox).

Can you tell me what "incomplete" about it?

 

[Cydney]

So, I cannot use a filename variable. I have to use the absolute directory
and filename? That's not good... No way around that, huh?
--
THX cs

Hi Cydney,

I can see two problems. First, you need to put the actual path and
filename into the [Excel 8.0....;] , not the name of the variable
containing it. Second, the sheet name should be in square brackets but
not parentheses.

Something like

Dim strSQL As String
strSQL = "INSERT INTO [EACXLS-Raw] " _
& "SELECT F1, F2, F3, F4, F5, F6, F7, " _
& "F8, F9, F10, F11, F12, F11, F13, F14 " _
& "FROM [Excel 8.0;HDR=No;database=" _
& MyImportedFile & ";].[Clean$];"
CurrentDb.Execute strSQL, dbFailOnError

Hi John,
I found this post and gave it a try on my database and I'm getting an error.
It says, "Syntax error in query. Incomplete query clause."

Here's my code:
DoCmd.RunSQL "INSERT INTO [EACXLS-Raw] SELECT F1, F2, F3, F4, F5, F6, F7,
F8, F9, F10, F11, F12, F11, F13, F14 FROM [Excel
8.0;HDR=No;database=MyImportedFile].([Clean$]);"

MyImportedFile is the path and file name of the excel file (received through
a Select File inputbox).

Can you tell me what "incomplete" about it?

 

[John Nurick]

Au contraire. Unlike some languages, VBA won't let you just slip a variable
name into literal text and then substitute the value later; but all you have
to do is to explicitly concatenate the string. The code snippet in my
previous post, introduced by the cryptic words "Something like", shows how.

So, I cannot use a filename variable. I have to use the absolute directory
and filename? That's not good... No way around that, huh?
--
THX cs

Hi Cydney,

I can see two problems. First, you need to put the actual path and
filename into the [Excel 8.0....;] , not the name of the variable
containing it. Second, the sheet name should be in square brackets but
not parentheses.

Something like

Dim strSQL As String
strSQL = "INSERT INTO [EACXLS-Raw] " _
& "SELECT F1, F2, F3, F4, F5, F6, F7, " _
& "F8, F9, F10, F11, F12, F11, F13, F14 " _
& "FROM [Excel 8.0;HDR=No;database=" _
& MyImportedFile & ";].[Clean$];"
CurrentDb.Execute strSQL, dbFailOnError

Hi John,
I found this post and gave it a try on my database and I'm getting an
error.
It says, "Syntax error in query. Incomplete query clause."

Here's my code:
DoCmd.RunSQL "INSERT INTO [EACXLS-Raw] SELECT F1, F2, F3, F4, F5, F6,
F7,
F8, F9, F10, F11, F12, F11, F13, F14 FROM [Excel
8.0;HDR=No;database=MyImportedFile].([Clean$]);"

MyImportedFile is the path and file name of the excel file (received
through
a Select File inputbox).

Can you tell me what "incomplete" about it?

 

[Cydney]

You're absolutely right... I feel pretty stupid for missing that.
OK... The process of the import works.. right up until I get the error for
uknown field name: 'F1'.

I was getting that same error before with "transferspreadsheet" command and
thought I might have better results with the INSERT INTO command. But.. no.

My lines of import in the Excel file all look about like this one...
15.1D.10 COORD 1110115.00
1299676.00 -189561.00 1313757.00 20109.00 1.183400 1.00 -20109.00 1313757.00 -203642.00 19642.40 66.88

There is no header row. What's wrong??
--
THX cs

Au contraire. Unlike some languages, VBA won't let you just slip a variable
name into literal text and then substitute the value later; but all you have
to do is to explicitly concatenate the string. The code snippet in my
previous post, introduced by the cryptic words "Something like", shows how.

So, I cannot use a filename variable. I have to use the absolute directory
and filename? That's not good... No way around that, huh?
--
THX cs

Hi Cydney,

I can see two problems. First, you need to put the actual path and
filename into the [Excel 8.0....;] , not the name of the variable
containing it. Second, the sheet name should be in square brackets but
not parentheses.

Something like

Dim strSQL As String
strSQL = "INSERT INTO [EACXLS-Raw] " _
& "SELECT F1, F2, F3, F4, F5, F6, F7, " _
& "F8, F9, F10, F11, F12, F11, F13, F14 " _
& "FROM [Excel 8.0;HDR=No;database=" _
& MyImportedFile & ";].[Clean$];"
CurrentDb.Execute strSQL, dbFailOnError

On Mon, 3 Oct 2005 12:45:03 -0700, Cydney

Hi John,
I found this post and gave it a try on my database and I'm getting an
error.
It says, "Syntax error in query. Incomplete query clause."

Here's my code:
DoCmd.RunSQL "INSERT INTO [EACXLS-Raw] SELECT F1, F2, F3, F4, F5, F6,
F7,
F8, F9, F10, F11, F12, F11, F13, F14 FROM [Excel
8.0;HDR=No;database=MyImportedFile].([Clean$]);"

MyImportedFile is the path and file name of the excel file (received
through
a Select File inputbox).

Can you tell me what "incomplete" about it?