Running Office 2000 Pro, Utilizing Excel to generate
reports and data entry, however the different types of
data and the amount of data really should be in a
database, so I am reworking the application. But having a
problem.

I have tried several examples from the VB help files,
books, and suggestions from others to do updates to an
Access database from the Excel spread sheet, I am able to
query the data into the sheet but not able to update back
to access.

When I try using Workspace or Database as a type I
receive invalid user defined type error message. Is there
a definition that I need or some addin that I'm missing?
Nothing has clearly stated where these come from?

The code I use for querying was generated using the
record macro and selecting from the menues the external
data new query selection

With ActiveSheet.QueryTables.Add(Connection:=Array
(Array( _
"ODBC;DSN=MS Access
Database;DBQ=H:\dvctracking\DVCTracking.mdb;Defaul tDir=H:\
d
vctracking;DriverId=25;FIL=MS Access;MaxBufferSize=204" _
), Array("8;PageTimeout=5;")), Destination:=Range
("A1"))
.CommandText = Array( _
"SELECT HomeBase.HomeBase" & Chr(13) & "" & Chr
(10) & "FROM `H:\dvctracking\DVCTracking`.HomeBase
HomeBase" _
)
.Name = "Query from MS Access Database"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = True
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.Refresh BackgroundQuery:=False
End With

What is it I'm missing or overlooking, I've tried to
modify the above query command to do the updates by
changing the sql statment, modifing the different options
and commands and still have had no luck as well

This might be a help for getting data to and from Excel and Access: It
includes examples of using variables in SQL queries.
http://www.bygsoftware.com/examples/sql.html

Or you can get there from the "Excel with Access Databases" section on page:
http://www.bygsoftware.com/examples/examples.htm

It demonstrates how to use SQL in Excel's VBA to:

* create a database,
* create a table
* insert records
* select records,
* update records,
* delete records,
* delete a table,
* delete a database.

DAO and ADO files available.

You can also download the demonstration file called "excelsql.zip".

The code is open and commented.


--
Regards
Andy Wiggins
www.BygSoftware.com
Home of "Save and BackUp",
"The Excel Auditor" and "Byg Tools for VBA"



"David Slicer" wrote in message
...
Running Office 2000 Pro, Utilizing Excel to generate
reports and data entry, however the different types of
data and the amount of data really should be in a
database, so I am reworking the application. But having a
problem.

I have tried several examples from the VB help files,
books, and suggestions from others to do updates to an
Access database from the Excel spread sheet, I am able to
query the data into the sheet but not able to update back
to access.

When I try using Workspace or Database as a type I
receive invalid user defined type error message. Is there
a definition that I need or some addin that I'm missing?
Nothing has clearly stated where these come from?

The code I use for querying was generated using the
record macro and selecting from the menues the external
data new query selection

With ActiveSheet.QueryTables.Add(Connection:=Array
(Array( _
"ODBC;DSN=MS Access
Database;DBQ=H:\dvctracking\DVCTracking.mdb;Defaul tDir=H:\
d
vctracking;DriverId=25;FIL=MS Access;MaxBufferSize=204" _
), Array("8;PageTimeout=5;")), Destination:=Range
("A1"))
.CommandText = Array( _
"SELECT HomeBase.HomeBase" & Chr(13) & "" & Chr
(10) & "FROM `H:\dvctracking\DVCTracking`.HomeBase
HomeBase" _
)
.Name = "Query from MS Access Database"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = True
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.Refresh BackgroundQuery:=False
End With

What is it I'm missing or overlooking, I've tried to
modify the above query command to do the updates by
changing the sql statment, modifing the different options
and commands and still have had no luck as well

This is everthing I have tried, with no luck, when I
use "Dim DbName as Database" I get "User Defined-Type Not
Found" and the application quits.

I get the same thing for Recordset, Workspace, Tables,
etc. none of them seem to be available where are their
types defined. that seems to be the missing ingreedance
at this point.


-----Original Message-----
This might be a help for getting data to and from Excel
and Access: It
includes examples of using variables in SQL queries.
http://www.bygsoftware.com/examples/sql.html

Or you can get there from the "Excel with Access
Databases" section on page:
http://www.bygsoftware.com/examples/examples.htm

It demonstrates how to use SQL in Excel's VBA to:

* create a database,
* create a table
* insert records
* select records,
* update records,
* delete records,
* delete a table,
* delete a database.

DAO and ADO files available.

You can also download the demonstration file
called "excelsql.zip".

The code is open and commented.


--
Regards
Andy Wiggins
www.BygSoftware.com
Home of "Save and BackUp",
"The Excel Auditor" and "Byg Tools for VBA"



"David Slicer" wrote in message
...
Running Office 2000 Pro, Utilizing Excel to generate
reports and data entry, however the different types of
data and the amount of data really should be in a
database, so I am reworking the application. But
having a
problem.

I have tried several examples from the VB help files,
books, and suggestions from others to do updates to an
Access database from the Excel spread sheet, I am able
to
query the data into the sheet but not able to update
back
to access.

When I try using Workspace or Database as a type I
receive invalid user defined type error message. Is
there
a definition that I need or some addin that I'm
missing?
Nothing has clearly stated where these come from?

The code I use for querying was generated using the
record macro and selecting from the menues the external
data new query selection

With ActiveSheet.QueryTables.Add(Connection:=Array
(Array( _
"ODBC;DSN=MS Access

Database;DBQ=H:\dvctracking\DVCTracking.mdb;Defaul tDir=H:\
d
vctracking;DriverId=25;FIL=MS
Access;MaxBufferSize=204" _
), Array("8;PageTimeout=5;")),
Destination:=Range
("A1"))
.CommandText = Array( _
"SELECT HomeBase.HomeBase" & Chr(13) & "" & Chr
(10) & "FROM `H:\dvctracking\DVCTracking`.HomeBase
HomeBase" _
)
.Name = "Query from MS Access Database"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = True
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.Refresh BackgroundQuery:=False
End With

What is it I'm missing or overlooking, I've tried to
modify the above query command to do the updates by
changing the sql statment, modifing the different
options
and commands and still have had no luck as well




.

In the Visual Basic Editor, choose ToolsReferences.
Depending on your code, set a reference to either Microsoft ActiveX Data
Objects 2.x Library or Microsoft DAO 3.x Object Library.

David Slicer wrote:
This is everthing I have tried, with no luck, when I
use "Dim DbName as Database" I get "User Defined-Type Not
Found" and the application quits.

I get the same thing for Recordset, Workspace, Tables,
etc. none of them seem to be available where are their
types defined. that seems to be the missing ingreedance
at this point.



-----Original Message-----
This might be a help for getting data to and from Excel

and Access: It

includes examples of using variables in SQL queries.
http://www.bygsoftware.com/examples/sql.html

Or you can get there from the "Excel with Access

Databases" section on page:

http://www.bygsoftware.com/examples/examples.htm

It demonstrates how to use SQL in Excel's VBA to:

* create a database,
* create a table
* insert records
* select records,
* update records,
* delete records,
* delete a table,
* delete a database.

DAO and ADO files available.

You can also download the demonstration file

called "excelsql.zip".

The code is open and commented.


--
Regards
Andy Wiggins
www.BygSoftware.com
Home of "Save and BackUp",
"The Excel Auditor" and "Byg Tools for VBA"



"David Slicer" wrote in message
.. .

Running Office 2000 Pro, Utilizing Excel to generate
reports and data entry, however the different types of
data and the amount of data really should be in a
database, so I am reworking the application. But

having a

problem.

I have tried several examples from the VB help files,
books, and suggestions from others to do updates to an
Access database from the Excel spread sheet, I am able

to

query the data into the sheet but not able to update

back

to access.

When I try using Workspace or Database as a type I
receive invalid user defined type error message. Is

there

a definition that I need or some addin that I'm

missing?

Nothing has clearly stated where these come from?

The code I use for querying was generated using the
record macro and selecting from the menues the external
data new query selection

With ActiveSheet.QueryTables.Add(Connection:=Array
(Array( _
"ODBC;DSN=MS Access


Database;DBQ=H:\dvctracking\DVCTracking.mdb;Defaul tDir=H:\

d
vctracking;DriverId=25;FIL=MS

Access;MaxBufferSize=204" _

), Array("8;PageTimeout=5;")),

Destination:=Range

("A1"))
.CommandText = Array( _
"SELECT HomeBase.HomeBase" & Chr(13) & "" & Chr
(10) & "FROM `H:\dvctracking\DVCTracking`.HomeBase
HomeBase" _
)
.Name = "Query from MS Access Database"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = True
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.Refresh BackgroundQuery:=False
End With

What is it I'm missing or overlooking, I've tried to
modify the above query command to do the updates by
changing the sql statment, modifing the different

options

and commands and still have had no luck as well




.




--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

Thanks Debra that looks like the answer to my problem will
give it a try

-----Original Message-----
In the Visual Basic Editor, choose ToolsReferences.
Depending on your code, set a reference to either
Microsoft ActiveX Data
Objects 2.x Library or Microsoft DAO 3.x Object Library.

David Slicer wrote:
This is everthing I have tried, with no luck, when I
use "Dim DbName as Database" I get "User Defined-Type
Not
Found" and the application quits.

I get the same thing for Recordset, Workspace, Tables,
etc. none of them seem to be available where are their
types defined. that seems to be the missing ingreedance
at this point.



-----Original Message-----
This might be a help for getting data to and from Excel

and Access: It

includes examples of using variables in SQL queries.
http://www.bygsoftware.com/examples/sql.html

Or you can get there from the "Excel with Access

Databases" section on page:

http://www.bygsoftware.com/examples/examples.htm

It demonstrates how to use SQL in Excel's VBA to:

* create a database,
* create a table
* insert records
* select records,
* update records,
* delete records,
* delete a table,
* delete a database.

DAO and ADO files available.

You can also download the demonstration file

called "excelsql.zip".

The code is open and commented.


--
Regards
Andy Wiggins
www.BygSoftware.com
Home of "Save and BackUp",
"The Excel Auditor" and "Byg Tools for VBA"



"David Slicer" wrote in message
. ..

Running Office 2000 Pro, Utilizing Excel to generate
reports and data entry, however the different types of
data and the amount of data really should be in a
database, so I am reworking the application. But

having a

problem.

I have tried several examples from the VB help files,
books, and suggestions from others to do updates to an
Access database from the Excel spread sheet, I am able

to

query the data into the sheet but not able to update

back

to access.

When I try using Workspace or Database as a type I
receive invalid user defined type error message. Is

there

a definition that I need or some addin that I'm

missing?

Nothing has clearly stated where these come from?

The code I use for querying was generated using the
record macro and selecting from the menues the external
data new query selection

With ActiveSheet.QueryTables.Add(Connection:=Array
(Array( _
"ODBC;DSN=MS Access



Database;DBQ=H:\dvctracking\DVCTracking.mdb;Defaul tDir=H:\

d
vctracking;DriverId=25;FIL=MS

Access;MaxBufferSize=204" _

), Array("8;PageTimeout=5;")),

Destination:=Range

("A1"))
.CommandText = Array( _
"SELECT HomeBase.HomeBase" & Chr(13) & "" & Chr
(10) & "FROM `H:\dvctracking\DVCTracking`.HomeBase
HomeBase" _
)
.Name = "Query from MS Access Database"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = True
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.Refresh BackgroundQuery:=False
End With

What is it I'm missing or overlooking, I've tried to
modify the above query command to do the updates by
changing the sql statment, modifing the different

options

and commands and still have had no luck as well




.




--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

.