Only two options to import Excel files?

[PW]

Convert them to CSV in Excel, then use TransferText (which does not
work correctly and also doesn't accept XLS/Excel files directly) or
create a link to an Excel XLS workbook and do an Append Query (as some
have suggested here)??

I find that hard to believe as Access is part of MS Office and Visual
FoxPro is not. I split my time using both and I can do a simple copy
command to XLS in VFP or an Append command to DBF or use Office
Automation to build workbooks using VFP with ease. I've been doing
this for years.

TransferText is incorrectly transposing some information as blank
fields (seems like a crap shoot). I don't know what the CSV (wish it
could be an XLS) file is until I return the file name and path from a
user dialog box that selects the file.

-pw

[lyle fairfield]

Is there a question here?

On May 19, 11:18*pm, PW <[e-mail]>
wrote:

Convert them to CSV in Excel, then use TransferText (which does not
work correctly and also doesn't accept XLS/Excel files directly) or
create a link to an Excel XLS workbook and do an Append Query (as some
have suggested here)??

I find that hard to believe as Access is part of MS Office and Visual
FoxPro is not. *I split my time using both and I can do a simple copy
command to XLS in VFP or an Append command to DBF or use Office
Automation to build workbooks using VFP with ease. *I've been doing
this for years.

TransferText is incorrectly transposing some information as blank
fields (seems like a crap shoot). *I don't know what the CSV (wish it
could be an XLS) file is until I return the file name and path from a
user dialog box that selects the file.

-pw

[PW]

On Mon, 19 May 2008 20:27:04 -0700 (PDT), lyle fairfield
<ly************@gmail.comwrote:

>Is there a question here?

Do I have any other options????

-pw

>
On May 19, 11:18*pm, PW <[e-mail]>
wrote:

>Convert them to CSV in Excel, then use TransferText (which does not
work correctly and also doesn't accept XLS/Excel files directly) or
create a link to an Excel XLS workbook and do an Append Query (as some
have suggested here)??

I find that hard to believe as Access is part of MS Office and Visual
FoxPro is not. *I split my time using both and I can do a simple copy
command to XLS in VFP or an Append command to DBF or use Office
Automation to build workbooks using VFP with ease. *I've been doing
this for years.

TransferText is incorrectly transposing some information as blank
fields (seems like a crap shoot). *I don't know what the CSV (wish it
could be an XLS) file is until I return the file name and path from a
user dialog box that selects the file.

-pw

[Sky]

"PW" <pa**********************@removehotmail.comwrote in message
news:7j********************************@4ax.com...

>
For instance, is there any command like this in Access 2003. This is
from Visual FoxPro8 which has been bashed for years but yet as not
part of the MS Office suite and not part of Visual Studio can still
transfer data to and from Excel with ease (and to a DBF file)!:

Adds records to the end of the currently selected table from another
file.

Here is some sample SQL for a query to insert records into an Access table
named MyAccessTable from an Excel file named C:\Sample\MyExcelFile.xls:

INSERT INTO MyAccessTable (FieldA, FieldB) SELECT FieldA, FieldB FROM [Excel
8.0;HDR=YES;IMEX=2;DATABASE=C:\Sample\MyExcelFile. xls].[MyExcelWorksheet];

You can do similar things with text files or dBase files.

- Steve

[PW]

On Tue, 20 May 2008 14:47:31 GMT, "Sky" <sky @ stanley associates .
comwrote:

>
"PW" <pa**********************@removehotmail.comwrote in message
news:7j********************************@4ax.com.. .

>>
For instance, is there any command like this in Access 2003. This is
from Visual FoxPro8 which has been bashed for years but yet as not
part of the MS Office suite and not part of Visual Studio can still
transfer data to and from Excel with ease (and to a DBF file)!:

Adds records to the end of the currently selected table from another
file.

Here is some sample SQL for a query to insert records into an Access table
named MyAccessTable from an Excel file named C:\Sample\MyExcelFile.xls:

INSERT INTO MyAccessTable (FieldA, FieldB) SELECT FieldA, FieldB FROM [Excel
8.0;HDR=YES;IMEX=2;DATABASE=C:\Sample\MyExcelFile .xls].[MyExcelWorksheet];

You can do similar things with text files or dBase files.

- Steve

Nice Steve! I will try that tomorrow. I assume I create an Access
table first so I can have field names?

-paul

[Sky]

"PW" <pa**********************@removehotmail.comwrote in message
news:ea********************************@4ax.com...

On Tue, 20 May 2008 14:47:31 GMT, "Sky" <sky @ stanley associates .
comwrote:

>>
"PW" <pa**********************@removehotmail.comwrote in message
news:7j********************************@4ax.com. ..

>>>
For instance, is there any command like this in Access 2003. This is
from Visual FoxPro8 which has been bashed for years but yet as not
part of the MS Office suite and not part of Visual Studio can still
transfer data to and from Excel with ease (and to a DBF file)!:

Adds records to the end of the currently selected table from another
file.

Here is some sample SQL for a query to insert records into an Access table
named MyAccessTable from an Excel file named C:\Sample\MyExcelFile.xls:

INSERT INTO MyAccessTable (FieldA, FieldB) SELECT FieldA, FieldB FROM
[Excel
8.0;HDR=YES;IMEX=2;DATABASE=C:\Sample\MyExcelFil e.xls].[MyExcelWorksheet];

You can do similar things with text files or dBase files.

- Steve

Nice Steve! I will try that tomorrow. I assume I create an Access
table first so I can have field names?

-paul

You can append to an existing table, or create a make-table query directly
from Excel, just like from any other data source. Of course, I never
recommend make-table queries in production, since you do not get precisely
defined field specifications, key fields, indexes, etc.

- Steve

[PW]

On Tue, 20 May 2008 14:47:31 GMT, "Sky" <sky @ stanley associates .
comwrote:

>
"PW" <pa**********************@removehotmail.comwrote in message
news:7j********************************@4ax.com.. .

>>
For instance, is there any command like this in Access 2003. This is
from Visual FoxPro8 which has been bashed for years but yet as not
part of the MS Office suite and not part of Visual Studio can still
transfer data to and from Excel with ease (and to a DBF file)!:

Adds records to the end of the currently selected table from another
file.

Here is some sample SQL for a query to insert records into an Access table
named MyAccessTable from an Excel file named C:\Sample\MyExcelFile.xls:

INSERT INTO MyAccessTable (FieldA, FieldB) SELECT FieldA, FieldB FROM [Excel
8.0;HDR=YES;IMEX=2;DATABASE=C:\Sample\MyExcelFile .xls].[MyExcelWorksheet];

You can do similar things with text files or dBase files.

- Steve

Not working for me. Access doesn't like tblTempCashReg. Syntax
error:

Insert into
rstTempCashReg(manufacturer,description,barcode,co lor,styleormodel,size,qty,oldplu,cost,retail)
Select
manufacturer,description,barcode,color,styleormode l,size,qty,oldplu,cost,retail
From [Excel8.0;HDR=YES;IMEX=2;DATABASE= strReturnVal].[Sheet1]

[lyle fairfield]

I don't like using recordsets but in cases where the import is gnarly,
they can let one proceed a step at a time, first establishing a
connection, then populating the recordset, then modifying the data,
and finally, inserting it into a table. The code can be written and
tested bit by bit.

I had three ways of doing what this code does, and can actually
remember one of the others, which was to link to the Excel file
through SQL-Server.

Option Base 0
Option Compare Database
Option Explicit

Sub UploadDataFromOfficialProjectedEnrolment(ByVal ExcelFilePath$,
ByVal ExclusivelyFrenchImmersion$, ByVal LateFrenchImmersion$)
Dim LocalConnection As ADODB.Connection
Dim Enrolment&
Dim PreviousSchoolName$
Dim ProgramID
Dim SchoolsAndEnrolment As ADODB.Recordset
Dim SchoolID&
Dim SchoolNameField As ADODB.Field
Dim SchoolName$
Dim SQL$
Dim Areas$(0 To 2)
Dim y&
Dim z&

'-----------------

ExclusivelyFrenchImmersion = "," & Trim(ExclusivelyFrenchImmersion) &
","
LateFrenchImmersion = "," & Trim(LateFrenchImmersion) & ","

'-----------------

Set LocalConnection = New ADODB.Connection
With LocalConnection
.Provider = "Microsoft.Jet.OLEDB.4.0"
.Properties.Item("Data Source") = ExcelFilePath
.Properties.Item("Extended Properties") = "Excel 8.0"
.Open
End With

'-----------------

Areas(0) = "EAST$"
Areas(1) = "NORTH$"
Areas(2) = "WEST$"

SQL = "DELETE FROM Schools"
CurrentProject.Connection.Execute (SQL)

SQL = "SELECT * FROM [AREA]"
SQL = SQL & vbNewLine
SQL = SQL & "WHERE [School Name] IS NOT NULL"
SQL = SQL & vbNewLine
SQL = SQL & "AND [School Name] <'Total'"
SQL = SQL & vbNewLine
SQL = SQL & "AND [School Name] <'Grand Total'"
SQL = SQL & vbNewLine
SQL = SQL & "AND [F14] IS NOT NULL"

For y = 0 To 2
Set SchoolsAndEnrolment = LocalConnection.Execute(Replace(SQL,
"AREA", Areas(y)))
With SchoolsAndEnrolment
Set SchoolNameField = .Fields(0)
While Not .EOF
SchoolName = StrConv(Replace(Trim(SchoolNameField.Value),
"'", ""), vbUpperCase)
If Len(SchoolName) 0 And InStr(SchoolName, "TOTAL") = 0
Then
ProgramID = 2
If (PreviousSchoolName = SchoolName) And
(InStr(LateFrenchImmersion, "," & SchoolName & ",") <0) Then
ProgramID = 3
If PreviousSchoolName <SchoolName Then
CurrentProject.Connection.Execute "INSERT INTO
Schools ([Name]) VALUES ('" & Replace(SchoolName, "'", "") & "')"
SchoolID =
CurrentProject.Connection.Execute("SELECT @@Identity")(0)
If InStr(ExclusivelyFrenchImmersion, "," &
SchoolName & ",") = 0 Then ProgramID = 1
PreviousSchoolName = SchoolName
End If
For z = 1 To 11
Enrolment = Nz(.Fields(z).Value, 0)
If Enrolment 0 Then
CurrentProject.Connection.Execute _
"INSERT INTO Enrolments (School, Program,
Class, Enrolment) VALUES (" _
& SchoolID & "," & ProgramID & "," & z & "," &
Enrolment & ")"
End If
Next z
End If
.MoveNext
Wend
End With
Next y

MsgBox "All Done Uploading Schools and Enrolment", vbInformation,
"ffdba"
On Error Resume Next
DoCmd.Close acForm, "UploadNewSchoolData"

End Sub


On May 21, 6:14*pm, PW <[e-mail]>
wrote:

On Tue, 20 May 2008 14:47:31 GMT, "Sky" <sky @ stanley associates .
Not working for me. *Access doesn't like tblTempCashReg. *Syntax

[Sky]

"PW" <pa**********************@removehotmail.comwrote in message
news:9g********************************@4ax.com...

On Tue, 20 May 2008 14:47:31 GMT, "Sky" <sky @ stanley associates .
comwrote:

>>
"PW" <pa**********************@removehotmail.comwrote in message
news:7j********************************@4ax.com. ..

>>>
For instance, is there any command like this in Access 2003. This is
from Visual FoxPro8 which has been bashed for years but yet as not
part of the MS Office suite and not part of Visual Studio can still
transfer data to and from Excel with ease (and to a DBF file)!:

Adds records to the end of the currently selected table from another
file.

Here is some sample SQL for a query to insert records into an Access table
named MyAccessTable from an Excel file named C:\Sample\MyExcelFile.xls:

INSERT INTO MyAccessTable (FieldA, FieldB) SELECT FieldA, FieldB FROM
[Excel
8.0;HDR=YES;IMEX=2;DATABASE=C:\Sample\MyExcelFil e.xls].[MyExcelWorksheet];

You can do similar things with text files or dBase files.

- Steve

Not working for me. Access doesn't like tblTempCashReg. Syntax
error:

Insert into
rstTempCashReg(manufacturer,description,barcode,co lor,styleormodel,size,qty,oldplu,cost,retail)
Select
manufacturer,description,barcode,color,styleormode l,size,qty,oldplu,cost,retail
From [Excel8.0;HDR=YES;IMEX=2;DATABASE= strReturnVal].[Sheet1]

Is rstTempCashReg the name of a table? The prefix indicates a recordset
name.

Also there is no space after rstTempCashReg and before the parenthesis.

And is "strReturnVal" the literal name of an Excel spreadsheet file? It
should end with .xls. If it is a string, then you need to concatenate it
with the SQL as a string.

Can you make a simple Select query from Excel, such as the following SQL?:

Select
manufacturer,description,barcode,color,styleormode l,size,qty,oldplu,cost,retail
From [Excel8.0;HDR=YES;IMEX=2;DATABASE= YourSpreadsheetName.xls].[Sheet1]

If you CAN see the Select query datasheet, then you know you have an error
in the "Insert Into" part of the query.

If you CANNOT see the Select query datasheet, then you need to check the
Select part with your Excel file name.

But offhand, it looks like none of the names are correct, and you have some
debugging to do.

- Steve

[PW]

On Thu, 22 May 2008 01:32:55 GMT, "Sky" <sky @ stanley associates .
comwrote:

>"PW" <pa**********************@removehotmail.comwrote in message
news:9g********************************@4ax.com.. .

>On Tue, 20 May 2008 14:47:31 GMT, "Sky" <sky @ stanley associates .
comwrote:

>>>
"PW" <pa**********************@removehotmail.comwrote in message
news:7j********************************@4ax.com ...

For instance, is there any command like this in Access 2003. This is
from Visual FoxPro8 which has been bashed for years but yet as not
part of the MS Office suite and not part of Visual Studio can still
transfer data to and from Excel with ease (and to a DBF file)!:

Adds records to the end of the currently selected table from another
file.
Here is some sample SQL for a query to insert records into an Access table
named MyAccessTable from an Excel file named C:\Sample\MyExcelFile.xls:

INSERT INTO MyAccessTable (FieldA, FieldB) SELECT FieldA, FieldB FROM
[Excel
8.0;HDR=YES;IMEX=2;DATABASE=C:\Sample\MyExcelFi le.xls].[MyExcelWorksheet];

You can do similar things with text files or dBase files.

- Steve

Not working for me. Access doesn't like tblTempCashReg. Syntax
error:

Insert into
rstTempCashReg(manufacturer,description,barcode,c olor,styleormodel,size,qty,oldplu,cost,retail)
Select
manufacturer,description,barcode,color,styleormod el,size,qty,oldplu,cost,retail
From [Excel8.0;HDR=YES;IMEX=2;DATABASE= strReturnVal].[Sheet1]

Is rstTempCashReg the name of a table? The prefix indicates a recordset
name.

Also there is no space after rstTempCashReg and before the parenthesis.

And is "strReturnVal" the literal name of an Excel spreadsheet file? It
should end with .xls. If it is a string, then you need to concatenate it
with the SQL as a string.

Can you make a simple Select query from Excel, such as the following SQL?:

Select
manufacturer,description,barcode,color,styleormod el,size,qty,oldplu,cost,retail
From [Excel8.0;HDR=YES;IMEX=2;DATABASE= YourSpreadsheetName.xls].[Sheet1]

If you CAN see the Select query datasheet, then you know you have an error
in the "Insert Into" part of the query.

If you CANNOT see the Select query datasheet, then you need to check the
Select part with your Excel file name.

But offhand, it looks like none of the names are correct, and you have some
debugging to do.

- Steve

I am getting a "Cannot find installable ISAM" message in Access with
this code in SQL view when I try to run it:

Select
manufacturer,description,barcode,color,styleormode l,size,qty,oldplu,cost,retail
From [Excel8.0;HDR=YES;IMEX=2;DATABASE=
C:\SD\Access2003\Lodgeical\clientversions\JoshuaCr eek\cashregister20080501.xls].[Sheet1]

The code will not save in the editor to begin with. It is in Red.

Thanks,

-pw

[PW]

What do the $ and & mean Lyle?

I am not using SQL Server, just an Access front and backend.

Thanks,

-paul
On Wed, 21 May 2008 16:56:14 -0700 (PDT), lyle fairfield
<ly************@gmail.comwrote:

>I don't like using recordsets but in cases where the import is gnarly,
they can let one proceed a step at a time, first establishing a
connection, then populating the recordset, then modifying the data,
and finally, inserting it into a table. The code can be written and
tested bit by bit.

I had three ways of doing what this code does, and can actually
remember one of the others, which was to link to the Excel file
through SQL-Server.

Option Base 0
Option Compare Database
Option Explicit

Sub UploadDataFromOfficialProjectedEnrolment(ByVal ExcelFilePath$,
ByVal ExclusivelyFrenchImmersion$, ByVal LateFrenchImmersion$)
Dim LocalConnection As ADODB.Connection
Dim Enrolment&
Dim PreviousSchoolName$
Dim ProgramID
Dim SchoolsAndEnrolment As ADODB.Recordset
Dim SchoolID&
Dim SchoolNameField As ADODB.Field
Dim SchoolName$
Dim SQL$
Dim Areas$(0 To 2)
Dim y&
Dim z&

'-----------------

ExclusivelyFrenchImmersion = "," & Trim(ExclusivelyFrenchImmersion) &
","
LateFrenchImmersion = "," & Trim(LateFrenchImmersion) & ","

'-----------------

Set LocalConnection = New ADODB.Connection
With LocalConnection
.Provider = "Microsoft.Jet.OLEDB.4.0"
.Properties.Item("Data Source") = ExcelFilePath
.Properties.Item("Extended Properties") = "Excel 8.0"
.Open
End With

'-----------------

Areas(0) = "EAST$"
Areas(1) = "NORTH$"
Areas(2) = "WEST$"

SQL = "DELETE FROM Schools"
CurrentProject.Connection.Execute (SQL)

SQL = "SELECT * FROM [AREA]"
SQL = SQL & vbNewLine
SQL = SQL & "WHERE [School Name] IS NOT NULL"
SQL = SQL & vbNewLine
SQL = SQL & "AND [School Name] <'Total'"
SQL = SQL & vbNewLine
SQL = SQL & "AND [School Name] <'Grand Total'"
SQL = SQL & vbNewLine
SQL = SQL & "AND [F14] IS NOT NULL"

For y = 0 To 2
Set SchoolsAndEnrolment = LocalConnection.Execute(Replace(SQL,
"AREA", Areas(y)))
With SchoolsAndEnrolment
Set SchoolNameField = .Fields(0)
While Not .EOF
SchoolName = StrConv(Replace(Trim(SchoolNameField.Value),
"'", ""), vbUpperCase)
If Len(SchoolName) 0 And InStr(SchoolName, "TOTAL") = 0
Then
ProgramID = 2
If (PreviousSchoolName = SchoolName) And
(InStr(LateFrenchImmersion, "," & SchoolName & ",") <0) Then
ProgramID = 3
If PreviousSchoolName <SchoolName Then
CurrentProject.Connection.Execute "INSERT INTO
Schools ([Name]) VALUES ('" & Replace(SchoolName, "'", "") & "')"
SchoolID =
CurrentProject.Connection.Execute("SELECT @@Identity")(0)
If InStr(ExclusivelyFrenchImmersion, "," &
SchoolName & ",") = 0 Then ProgramID = 1
PreviousSchoolName = SchoolName
End If
For z = 1 To 11
Enrolment = Nz(.Fields(z).Value, 0)
If Enrolment 0 Then
CurrentProject.Connection.Execute _
"INSERT INTO Enrolments (School, Program,
Class, Enrolment) VALUES (" _
& SchoolID & "," & ProgramID & "," & z & "," &
Enrolment & ")"
End If
Next z
End If
.MoveNext
Wend
End With
Next y

MsgBox "All Done Uploading Schools and Enrolment", vbInformation,
"ffdba"
On Error Resume Next
DoCmd.Close acForm, "UploadNewSchoolData"

End Sub


On May 21, 6:14*pm, PW <[e-mail]>
wrote:

>On Tue, 20 May 2008 14:47:31 GMT, "Sky" <sky @ stanley associates .
Not working for me. *Access doesn't like tblTempCashReg. *Syntax

[PW]

Lyle and Steve, I love your code!. And thanks so much for your help!

Guys, all I am trying to do is to come up with an equivalent in VBA
for this simple line of code that I do in Visual FoxPro about every
day (you should see the stuff I do with Office Automation and Excel,
Word,...with Visual FoxPro! I guess I just bragged - sorry about
that!;-):

Use tblImpData
Select tblImpData_pw
Append From k:\AttributionData\IMPORTDATA\chase.xls type XLS

Bingo! Batta Bing! That's all it takes. Show me that it can happen
in Access!! Don't mean to start a war here between Access and Visual
FoxPro. I love them both, but come on now!

VFP is not part of MS Office or Visual Studio so how come VFP can do
this so easily and appears impossible in Access (which is part of
Office)???????????????

The app's front end/mde is in Access 2003 and the backend mdb is
separated also in Access 2003. I need this for a distrubited mde so
we are not on site and do not have Access running.

-paulw

On Wed, 21 May 2008 16:56:14 -0700 (PDT), lyle fairfield
<ly************@gmail.comwrote:

>I don't like using recordsets but in cases where the import is gnarly,
they can let one proceed a step at a time, first establishing a
connection, then populating the recordset, then modifying the data,
and finally, inserting it into a table. The code can be written and
tested bit by bit.

I had three ways of doing what this code does, and can actually
remember one of the others, which was to link to the Excel file
through SQL-Server.

Option Base 0
Option Compare Database
Option Explicit

Sub UploadDataFromOfficialProjectedEnrolment(ByVal ExcelFilePath$,
ByVal ExclusivelyFrenchImmersion$, ByVal LateFrenchImmersion$)
Dim LocalConnection As ADODB.Connection
Dim Enrolment&
Dim PreviousSchoolName$
Dim ProgramID
Dim SchoolsAndEnrolment As ADODB.Recordset
Dim SchoolID&
Dim SchoolNameField As ADODB.Field
Dim SchoolName$
Dim SQL$
Dim Areas$(0 To 2)
Dim y&
Dim z&

'-----------------

ExclusivelyFrenchImmersion = "," & Trim(ExclusivelyFrenchImmersion) &
","
LateFrenchImmersion = "," & Trim(LateFrenchImmersion) & ","

'-----------------

Set LocalConnection = New ADODB.Connection
With LocalConnection
.Provider = "Microsoft.Jet.OLEDB.4.0"
.Properties.Item("Data Source") = ExcelFilePath
.Properties.Item("Extended Properties") = "Excel 8.0"
.Open
End With

'-----------------

Areas(0) = "EAST$"
Areas(1) = "NORTH$"
Areas(2) = "WEST$"

SQL = "DELETE FROM Schools"
CurrentProject.Connection.Execute (SQL)

SQL = "SELECT * FROM [AREA]"
SQL = SQL & vbNewLine
SQL = SQL & "WHERE [School Name] IS NOT NULL"
SQL = SQL & vbNewLine
SQL = SQL & "AND [School Name] <'Total'"
SQL = SQL & vbNewLine
SQL = SQL & "AND [School Name] <'Grand Total'"
SQL = SQL & vbNewLine
SQL = SQL & "AND [F14] IS NOT NULL"

For y = 0 To 2
Set SchoolsAndEnrolment = LocalConnection.Execute(Replace(SQL,
"AREA", Areas(y)))
With SchoolsAndEnrolment
Set SchoolNameField = .Fields(0)
While Not .EOF
SchoolName = StrConv(Replace(Trim(SchoolNameField.Value),
"'", ""), vbUpperCase)
If Len(SchoolName) 0 And InStr(SchoolName, "TOTAL") = 0
Then
ProgramID = 2
If (PreviousSchoolName = SchoolName) And
(InStr(LateFrenchImmersion, "," & SchoolName & ",") <0) Then
ProgramID = 3
If PreviousSchoolName <SchoolName Then
CurrentProject.Connection.Execute "INSERT INTO
Schools ([Name]) VALUES ('" & Replace(SchoolName, "'", "") & "')"
SchoolID =
CurrentProject.Connection.Execute("SELECT @@Identity")(0)
If InStr(ExclusivelyFrenchImmersion, "," &
SchoolName & ",") = 0 Then ProgramID = 1
PreviousSchoolName = SchoolName
End If
For z = 1 To 11
Enrolment = Nz(.Fields(z).Value, 0)
If Enrolment 0 Then
CurrentProject.Connection.Execute _
"INSERT INTO Enrolments (School, Program,
Class, Enrolment) VALUES (" _
& SchoolID & "," & ProgramID & "," & z & "," &
Enrolment & ")"
End If
Next z
End If
.MoveNext
Wend
End With
Next y

MsgBox "All Done Uploading Schools and Enrolment", vbInformation,
"ffdba"
On Error Resume Next
DoCmd.Close acForm, "UploadNewSchoolData"

End Sub


On May 21, 6:14*pm, PW <[e-mail]>
wrote:

>On Tue, 20 May 2008 14:47:31 GMT, "Sky" <sky @ stanley associates .
Not working for me. *Access doesn't like tblTempCashReg. *Syntax

[PW]

The syntax you supplied (I copied and pasted it on one line):

INSERT INTO MyAccessTable (FieldA, FieldB) SELECT FieldA, FieldB FROM
[Excel
8.0;HDR=YES;IMEX=2;DATABASE=C:\Sample\MyExcelFile. xls].[MyExcelWorksheet]

generates "compile error: expected end of statement" when I move the
cursor out of that line.

-pw

[PW]

strSQL = "Insert into tblTempCashReg
(manufacturer,description,barcode,color,styleormod el,size,qty,oldplu,cost,retail)
Select
manufacturer,description,barcode,color,styleormode l,size,qty,oldplu,cost,retail
From [Excel8.0;HDR=YES;IMEX=2;DATABASE= strReturnVal].[Sheet1]"

DoCmd.RunSQL strSQL

Generates "could not find installable ISAM"

-pw

[Salad]

PW wrote:

strSQL = "Insert into tblTempCashReg
(manufacturer,description,barcode,color,styleormod el,size,qty,oldplu,cost,retail)
Select
manufacturer,description,barcode,color,styleormode l,size,qty,oldplu,cost,retail
From [Excel8.0;HDR=YES;IMEX=2;DATABASE= strReturnVal].[Sheet1]"

DoCmd.RunSQL strSQL

Generates "could not find installable ISAM"

-pw

What is ;Database= strReturnVal? Is strReturnVal a new Access keyword?
Does Access like spaces in the string defining the source?

Before you even get into Inserting why don't you simply attempt to get
the Select query working first?

Get into the QBE, (Query/Design/New/Cancel) and select View/SQL from the
menu and paste the Select SQL into it and run and debug.

Last Dance
http://www.youtube.com/watch?v=WdTYcnUBADw

[PW]

On Thu, 22 May 2008 10:24:14 -0700, Salad <oi*@vinegar.comwrote:

>PW wrote:

> strSQL = "Insert into tblTempCashReg
(manufacturer,description,barcode,color,styleormo del,size,qty,oldplu,cost,retail)
Select
manufacturer,description,barcode,color,styleormod el,size,qty,oldplu,cost,retail
From [Excel8.0;HDR=YES;IMEX=2;DATABASE= strReturnVal].[Sheet1]"

DoCmd.RunSQL strSQL

Generates "could not find installable ISAM"

-pw

What is ;Database= strReturnVal? Is strReturnVal a new Access keyword?
Does Access like spaces in the string defining the source?

You're right - it's a syntax thing. This string stuff always drives
me up the wall! :-)

I am still messing with it, trying to get rid of the apostrophe,....

? strReturnVal

C:\SD\Access2003\Lodgeical\clientversions\JoshuaCr eek\cashregister20080501.xls

?strSQL

Insert into tblTempCashReg
(manufacturer,description,barcode,color,styleormod el,size,qty,oldplu,cost,retail)
Select
manufacturer,description,barcode,color,styleormode l,size,qty,oldplu,cost,retail
From [Excel8.0;HDR=YES;IMEX=2;DATABASE=
'C:\SD\Access2003\Lodgeical\clientversions\JoshuaC reek\cashregister20080501.xls']

I've gotten it down to "you can not import this file" <s>

>
Before you even get into Inserting why don't you simply attempt to get
the Select query working first?

Will do. Am trying to!

>Get into the QBE, (Query/Design/New/Cancel) and select View/SQL from the
menu and paste the Select SQL into it and run and debug.

Thanks!

[Salad]

PW wrote:

On Thu, 22 May 2008 10:24:14 -0700, Salad <oi*@vinegar.comwrote:

>>PW wrote:

>>strSQL = "Insert into tblTempCashReg
(manufacturer,description,barcode,color,styleor model,size,qty,oldplu,cost,retail)
Select
manufacturer,description,barcode,color,styleorm odel,size,qty,oldplu,cost,retail
From [Excel8.0;HDR=YES;IMEX=2;DATABASE= strReturnVal].[Sheet1]"

DoCmd.RunSQL strSQL

Generates "could not find installable ISAM"

-pw

What is ;Database= strReturnVal? Is strReturnVal a new Access keyword?
Does Access like spaces in the string defining the source?

You're right - it's a syntax thing. This string stuff always drives
me up the wall! :-)

I am still messing with it, trying to get rid of the apostrophe,....

? strReturnVal

C:\SD\Access2003\Lodgeical\clientversions\JoshuaCr eek\cashregister20080501.xls

?strSQL

Insert into tblTempCashReg
(manufacturer,description,barcode,color,styleormod el,size,qty,oldplu,cost,retail)
Select
manufacturer,description,barcode,color,styleormode l,size,qty,oldplu,cost,retail
From [Excel8.0;HDR=YES;IMEX=2;DATABASE=
'C:\SD\Access2003\Lodgeical\clientversions\JoshuaC reek\cashregister20080501.xls']

I've gotten it down to "you can not import this file" <s>

>>Before you even get into Inserting why don't you simply attempt to get
the Select query working first?

Will do. Am trying to!

>>Get into the QBE, (Query/Design/New/Cancel) and select View/SQL from the
menu and paste the Select SQL into it and run and debug.

Thanks!

Heres a routine that might help. First, do a File/GetExternalData/Link
to the spreadsheet. Now create a new code module and drop this code
into it.

Public Function MyConnect() As Long
Dim tdf As TableDef
Dim dbs As Database
Set dbs = CurrentDb
Set tdf = dbs.TableDefs("YourSpreadsheetName")

MsgBox tdf.Connect
Debug.Print tdf.Connect

dbs.Close
Set dbs = Nothing
End Function

In the Immediate window you'll have the connect string. It appears you
don't want to link this spreadsheet so by having the correct string you
can at least get close to what you want.

After you link, create your query and see if that works. Heck, it might
even be easier for you to link the spreadsheet, run a query on it, then
delete the link. You've spent a while on this problem. Time to figure
it out and move on to other things.

Math Club
http://www.youtube.com/watch?v=3r2C_4dwPh8

[PW]

On Thu, 22 May 2008 12:02:07 -0700, Salad <oi*@vinegar.comwrote:

>PW wrote:

>On Thu, 22 May 2008 10:24:14 -0700, Salad <oi*@vinegar.comwrote:

>>>PW wrote:

strSQL = "Insert into tblTempCashReg
(manufacturer,description,barcode,color,styleo rmodel,size,qty,oldplu,cost,retail)
Select
manufacturer,description,barcode,color,styleor model,size,qty,oldplu,cost,retail
From [Excel8.0;HDR=YES;IMEX=2;DATABASE= strReturnVal].[Sheet1]"

DoCmd.RunSQL strSQL

Generates "could not find installable ISAM"

-pw

What is ;Database= strReturnVal? Is strReturnVal a new Access keyword?
Does Access like spaces in the string defining the source?

You're right - it's a syntax thing. This string stuff always drives
me up the wall! :-)

I am still messing with it, trying to get rid of the apostrophe,....

? strReturnVal

C:\SD\Access2003\Lodgeical\clientversions\JoshuaC reek\cashregister20080501.xls

?strSQL

Insert into tblTempCashReg
(manufacturer,description,barcode,color,styleormo del,size,qty,oldplu,cost,retail)
Select
manufacturer,description,barcode,color,styleormod el,size,qty,oldplu,cost,retail
From [Excel8.0;HDR=YES;IMEX=2;DATABASE=
'C:\SD\Access2003\Lodgeical\clientversions\Joshua Creek\cashregister20080501.xls']

I've gotten it down to "you can not import this file" <s>

>>>Before you even get into Inserting why don't you simply attempt to get
the Select query working first?

Will do. Am trying to!

>>>Get into the QBE, (Query/Design/New/Cancel) and select View/SQL from the
menu and paste the Select SQL into it and run and debug.

Thanks!

Heres a routine that might help. First, do a File/GetExternalData/Link
to the spreadsheet. Now create a new code module and drop this code
into it.

Public Function MyConnect() As Long
Dim tdf As TableDef
Dim dbs As Database
Set dbs = CurrentDb
Set tdf = dbs.TableDefs("YourSpreadsheetName")

MsgBox tdf.Connect
Debug.Print tdf.Connect

dbs.Close
Set dbs = Nothing
End Function

In the Immediate window you'll have the connect string. It appears you
don't want to link this spreadsheet so by having the correct string you
can at least get close to what you want.

After you link, create your query and see if that works. Heck, it might
even be easier for you to link the spreadsheet, run a query on it, then
delete the link

Looks fantastic!

You've spent a while on this problem.

No kidding! And I am hardly making a $ on it (it's a request from one
of our clients) but it's worth it as I learned alot from every one
here.

>Time to figure
it out and move on to other things.

I just did. After all this, I just discovered the transferspreadsheet
function! LOL!

DoCmd.TransferSpreadsheet acImport, , "tblTempCashReg", strReturnVal,
-1

It works with the return value of the path to the spreadsheet.
Except, Excel is listing a bar code text box with values like
6.17867e+011 for some reason and that is what is getting imported to
the table. I can put the blame on the client and have them fix it.

Thanks for everyone's help!

-paul

>Math Club
http://www.youtube.com/watch?v=3r2C_4dwPh8

[CDMAPoster]

On May 22, 12:41*pm, PW <[e-mail]>
wrote:

*strSQL = "Insert into tblTempCashReg
(manufacturer,description,barcode,color,styleormod el,size,qty,oldplu,cost,r*etail)
Select
manufacturer,description,barcode,color,styleormode l,size,qty,oldplu,cost,re*tail
From [Excel8.0;HDR=YES;IMEX=2;DATABASE= strReturnVal].[Sheet1]"

DoCmd.RunSQL strSQL

Generates "could not find installable ISAM"

-pw

That's exactly what I would expect. In:

http://groups.google.com/group/comp....e5554ab679a9ce

I said:

"A2K dropped the Isam capability to link the FoxPro tables"

James A. Fortune
CD********@FortuneJames.com