Setting Up DAO connection to Excel 97 speedsheet

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Just can't get it right
Is it like this example?

Dim nWindEx As Database, tDef As TableDef
Dim dataSource As String
dataSource = _
"dBASE IV;DATABASE=C:\Program Files\Common Files\Microsoft Shared\MSquery"
Set nWindEx = Workspaces(0).OpenDatabase(Application.Path _
& "\NWINDEX.MDB")
Set tDef = nWindEx.CreateTableDef("Product")
tDef.Connect = dataSource
tDef.SourceTableName = "Product"
nWindEx.TableDefs.Append tDef
nWindEx.Close
 
Bricker said:
Just can't get it right
Is it like this example?

Dim nWindEx As Database, tDef As TableDef
Dim dataSource As String
dataSource = _
"dBASE IV;DATABASE=C:\Program Files\Common Files\Microsoft Shared\MSquery"
Set nWindEx = Workspaces(0).OpenDatabase(Application.Path _
& "\NWINDEX.MDB")
Set tDef = nWindEx.CreateTableDef("Product")
tDef.Connect = dataSource
tDef.SourceTableName = "Product"
nWindEx.TableDefs.Append tDef
nWindEx.Close
Click to expand...


Can't get it right? Seems like you've got it wrong in just
about every way possible ;-)

Your subject line says Excel while your connect string says
dBase, but the path is to some non-Access query.

A fairly simple way to figure out what the connect string
should be is to create a linked table (let's name it "Junk")
to the data using the menu item:
File - Get External Data - Link Tables

Then follow the wizard's steps to specify the data source.
When your done, you can just open the Junk table to make
sure that you are getting the correct data. After that is
squared away, then you can display the Access created
connect string by using this in the immediate window:

?DbEngine(0)(0),TableDefs!Junk.Connect

and then Copy/Paste it into your code and delete the Junk
table.
 
is that for Access 97.
Can't get the the code for the immediate window to come out right
runtime error 424 object required
 
:
All I want to do is connect up to excel 97 spreedsheet read whats there so I
can write in the Access database.
do I need these objects?
a workspace
a tabledef
If so how do I do that?

First I connect then I create the workspace then I create the table def when
do I get to the recordset?lol

I have all of the connection string just how do I get to a recordset from
that?

"Excel 5.0;HDR=YES;IMEX=2;DATABASE=path.xls;TABLE='sheetname'$"
 
Bricker said:
is that for Access 97.
Can't get the the code for the immediate window to come out right
runtime error 424 object required
Click to expand...


Doesn't matter what version. But I notice that I fat
fingered the reference with a comma instead of a dot. Try
this:
?DbEngine(0)(0).TableDefs!Junk.Connect
 
Bricker said:
All I want to do is connect up to excel 97 spreedsheet read whats there so I
can write in the Access database.
do I need these objects?
a workspace
a tabledef
If so how do I do that?

First I connect then I create the workspace then I create the table def when
do I get to the recordset?lol

I have all of the connection string just how do I get to a recordset from
that?

"Excel 5.0;HDR=YES;IMEX=2;DATABASE=path.xls;TABLE='sheetname'$"
Click to expand...

You should post your code so we can integrate an answer with
wht you already have.

Going back to your first post in this thread. Once you have
the TableDef properly set up, you can just open the
recordset:

. . .
Set rs = nWindEx.OpenRecordset("Product")
. . .
nWindEx.Close
 
Thanks for the help this is it!
It took a bit but the recordset runs through
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Dim ws As Workspace
Dim dbexcel As Database
Dim rcdex As Recordset
Dim tbldef As TableDef
Dim nWindEx As Database, tDef As TableDef
Dim dataSource As String
dataSource = _
"Excel 5.0;HDR=YES;IMEX=2;DATABASE=path and filename"
';TABLE='sheet name $'"
Set nWindEx = CurrentDb
nWindEx.connect = dataSource
Set tDef = nWindEx.CreateTableDef("sheet name$")
tDef.connect = dataSource
tDef.SourceTableName = "'sheet name$"
nWindEx.tabledefs.Append tDef
Set rcdex = tDef.OpenRecordset
rcdex.MoveFirst

Debug.Print rcdex.Fields(1)

nWindEx.Close
Set rcdex = Nothing
Set tDef = Nothing
Set nWindEx = Nothing
DoCmd.DeleteObject acTable, "'OMA VISA 1 DEC 05'$"
dataSource = ""
 
Yes, you can open a recordset without creating a tabledef, once
you have worked out the connect string.

set rs = codedb.openrecordset("select * from [database].
")

Use your connect string as [database], and your range or
worksheet name as
.

And if all you want is the field, you don't need an rs object:

=[application].[Currentdb].[OpenRecordset]('select * from [Excel
5.0;HDR=no;DATABASE=C:\Program Files\CAPIX\fred.xls].[a4:a4]').[Fields](0)

Also, once you have worked out the connect string you can
create a QueryDef instead of the Tabledef. Open a QueryDef
in design view and look for the Database property.

(david)
 
Back
Top