Hi Everyone,

I am seeming to have issue with some code I used years ago and now
doesnt seem to want to work. I have only a guess as to why - data type
issues with the Excel ISAM driver. I was wondering if anyone might
find five minutes to give this a try and let me know if it works for
them?

Private Sub test()
Dim dbEng As DAO.DBEngine
Dim dbCon As DAO.Database
Dim dbSQL As String

Set dbEng = New DAO.DBEngine
dbEng.CreateDatabase "C:\TryThis.mdb",
";LANGID=0x0409;CP=1252;COUNTRY=0", 64

Set dbCon = dbEng.OpenDatabase(ThisWorkbook.FullName, False, True,
"Excel 8.0;")
dbCon.Execute "SELECT * INTO [C:\TryThis.mdb].[Test] FROM [Sheet1$];"
dbCon.Close

If Not dbEng Is Nothing Then Set dbEng = Nothing
If Not dbCon Is Nothing Then Set dbCon = Nothing
End Sub

I was running this is Excel 2003 to (in theory) Access 2003. The goal
is to give some of my colleagues in my department the ability to
'push' data into an Access MDB file instead of having to re-acquire it
from a third party online data source which is painfully slow to work
with. ie/ They can just re-connect to the local mdb file instead.

The code above works fine up until the dbCon.Execute line, where I am
getting an error:
Run-time error '3274':
External table is not in the expected format.

I could have sworn that I used this successfully with A'97 and E'97.
Anyway, I have posted this in an Access newsgroup as I am hoping that
there is more in-depth knowledge of what the possible issue(s) might
be and possible solutions since I know just how smart and experienced
some of you are. Oh yeah, the code is being executed from Excel 2003.

I always thought that the 'SELECT * INTO.........' syntax would create
the target table as required, or maybe thats only purely inside
Access.

Any help appreciated.

Cheers

The Frog

Sample Data in Excel:

COL_A COL_B COL_C
try1 try4 try7
try2 try5 try8
try3 try6 try9

I placed all of this in Sheet1 starting with COL_A in position A1 and
finishing with try9 in C4.

Hi The Frog,

Alas, I have not to much experience with Excel, so I can not help you
very much.
All data manipulation on Excel files I do with .csv files.

Imb.


On Jan 31, 2:41*pm, The Frog <mr.frog.to.... (AT) googlemail (DOT) com> wrote:

The Frog wrote:

This was a long time ago, and I don't know if it was before
or after when you were doing it, but I definitely remember
some A2003 service pack (2?) removed the ability to update
Excel files from Access because of a law suit that MS lost.
I was doing something similar to what you are trying to do
at the time and refused to install that SP.

Sometine later, I applied a couple of hot fixes and got
Access into such a state that I could not install SP2
because it thought I already had it, but I could not install
SP3 because I had to have SP2 first. Eventually, I got it
straightened out when I wiped the HD and started over from
scratch. Fortunately, by then the project that needed to
update Excel files had gone away.

--
Marsh

Thanks for the feedback guys. I will keep hacking away at this one and
report back if I get it to work. I found a few clues snooping around
the net that might be useful. I might have to eliminate the dbCon part
and do it direct from the dbEng if I understand these clues correctly.
I am not sure how to implement that just yet, so it may take a little
playing around.......

I remember something too about the issues surounding data manipulation
with Excel. I think that was Enron? Maybe I am confusing it with
Sabanes Oxley. I dont deal with that issue on any regular basis, most
of my work is done in DB's and not in Excel. Excel does however have
some strong points for free form analysis, and my ultimate goal here
is to take some base data users have in Excel, pump it into an MDB,
collect a table of descriptive data from a server that matches one of
the columns of the user data and pump it into the same MDB, create a
join between the two tables, then let Jet do the structred output. Run
a few SQL queries against the MDB, bring back a recordset or two and
dump the results into the users original spreadsheet. The original
method of trying to do all this in Excel alone has already exceeded 70
pages of code for what could be done with a few SQL statements in a
database. Since I am building the 'next generation' of the tool I
thought I would improve on it a little :-)

Cheers and thanks

The Frog

Cheers

The Frog

Hi Everyone,

I am going to say up front that I am a firm believer in the phrase 'If
at first you dont succeed - cheat'. This has allowed me to produce
some ridiculuosly elegant solutions to some rather hairy and complex
problems, and I believe I will stick by this axiom. In this problems
case the answer was to change to ADO for a workable solution.

Sub adoTest()
Const dbLangGeneral As String = ";LANGID=0x0409;CP=1252;COUNTRY=0"

Dim adoCon As ADODB.Connection
Dim dbEng As DAO.DBEngine

Dim xSQL As String
Dim xCon As String
Dim xWbk As String
Dim xMDB As String

xMDB = "C:\TryThis.mdb"
Set dbEng = New DAO.DBEngine
'dbEng.CreateDatabase xMDB, dbLangGeneral, 64
Set dbEng = Nothing

xWbk = ThisWorkbook.FullName
xCon = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & xMDB & ";"
'xSQL = "INSERT INTO test_only SELECT * FROM [" &
ThisWorkbook.ActiveSheet.Name & "$] IN '" _
' & xWbk & "' 'Excel 8.0;'"
xSQL = "SELECT * INTO test_only FROM [" &
ThisWorkbook.ActiveSheet.Name & "$] IN '" _
& xWbk & "' 'Excel 8.0;'"

Set adoCon = New ADODB.Connection
With adoCon
.Open xCon
.CursorLocation = adUseClient
.Execute (xSQL)
End With
adoCon.Close
Set adoCon = Nothing
End Sub

There are two versions of the SQL statements to be executed. The first
is if your table already exists to receive data (place any autonum
fileds as the last field and not the first), and the second is if you
wish to create the table brand spanking new from the data in the
workbook. This is obscenely fast FWIW - it 'pushed' 5k records with 17
fields (columns) to a new table in under a second. Nice :-)

Now to late bind the thing and I'm good to go with this bit.....

Still cant figure out why this fails with DAO though. I would have
thought that the more native of the two methods would have had a
better chance of success. Maybe I am just stuffing it up somwhere.....

Thought I'd share the solution.

Cheers

The Frog

Here is the DAO only version. Helps if you get your connections
straight to begin with.........

Private Sub DAOtest()
Dim dbEng As DAO.DBEngine
Dim dbCon As DAO.Database
Dim dbSQL As String

dbSQL = "SELECT * INTO test_only FROM [" &
ThisWorkbook.ActiveSheet.Name & "$] IN '" _
& ThisWorkbook.FullName & "' 'Excel 8.0;'"

Set dbEng = New DAO.DBEngine
dbEng.CreateDatabase "C:\TryThis.mdb",
";LANGID=0x0409;CP=1252;COUNTRY=0", 64

Set dbCon = dbEng.OpenDatabase("C:\TryThis.mdb", False, False)
dbCon.Execute dbSQL
dbCon.Close

If Not dbEng Is Nothing Then Set dbEng = Nothing
If Not dbCon Is Nothing Then Set dbCon = Nothing
End Sub

Cheers

The Frog