Create a binary database from VBA

[zz]

Hi everyone:

I was wondering if there's a posibility of generating a SQL, DBF, etc..
database from within a VBA application [more specifically excel's VBA].

the thing is that it would be incredible helpful for my purposes and i
cannot use MDB or an external /third party database generator.


i really, really would preciate any wire you can shoot on me about this.


sooo much thanks in advantage.

 

[Cindy M.]

Hi Zz,

I was wondering if there's a posibility of generating a SQL, DBF, etc..
database from within a VBA application [more specifically excel's VBA].

the thing is that it would be incredible helpful for my purposes and i
cannot use MDB or an external /third party database generator.

i really, really would preciate any wire you can shoot on me about this.

There's nothing built-into VBA that will let you do this, beyond what's
provided in Excel's user interface under File/Save As. There are
conversion filters for DBF files, in various formats that will let you
create tables. You can't actually create an entire database, though.

You won't find a converter for SQL, however, as this is a server- rather
than file-based data source.

VBA lets you access other type libraries installed on a machine. This can
give you a wide range of capabilities. ADO with an OLE DB provider is
capable of generating data tables; you could probably also use T-SQL to
manipulate an SQL Server. All depends on what's installed on the
machine...

Cindy Meister
INTER-Solutions, Switzerland
http://homepage.swissonline.ch/cindymeister (last update Jun 17 2005)
http://www.word.mvps.org

This reply is posted in the Newsgroup; please post any follow question or
reply in the newsgroup and not by e-mail

 

[dolivastro]

We have a very large (enterprise size) SQL_Server data base. And we
use it as a backend in our excel operations. For example, a user can
type into a cell =My_Author(a1), and the function "My_Author" will jump
into the SQL database, find the author for the PaperKey found in cell
a1, and then enter the author into that cell. Similarly, the user can
enter =My_Institutiion (a1), and it returns the institution of the same
paper.

Is that what you need? Is so, the solution is a little complicated.
You would need to learn the ADO object.

Let me know
Dom

 

[zz]

i'm trying to use excel as a frontpage and foundation of my VBA application,
i cannot install Vb.NET or any other external application , i have to work
with XL.

i cannot install SQL server, not even access.

i tried using a very simple XML document as database, as i already know how
to generate and populate XML docs with VBA but it is not recommended since
it is not as optimized as a binary format, however i'm using this file
format to save my application's settings for every user as a simulated
personal profile.

then i tried using one XL sheet, but the file grew too much .

i'm gonna try Cindi's solution, i will create a DB4 from "Save as..." and
using the OLE db Provider insert and retrieve my values from this DB.






thanks to both of you



--


---
zz [MX]
cuasi-musico,semi-poeta y loco

We have a very large (enterprise size) SQL_Server data base. And we
use it as a backend in our excel operations. For example, a user can
type into a cell =My_Author(a1), and the function "My_Author" will jump
into the SQL database, find the author for the PaperKey found in cell
a1, and then enter the author into that cell. Similarly, the user can
enter =My_Institutiion (a1), and it returns the institution of the same
paper.

Is that what you need? Is so, the solution is a little complicated.
You would need to learn the ADO object.

Let me know
Dom

Hi everyone:

I was wondering if there's a posibility of generating a SQL, DBF, etc..
database from within a VBA application [more specifically excel's VBA].

the thing is that it would be incredible helpful for my purposes and i
cannot use MDB or an external /third party database generator.


i really, really would preciate any wire you can shoot on me about this.


sooo much thanks in advantage.

 

[Cindy M.]

Hi Zz,

Given this background, you *could* use the Jet Engine (DAO) to create and
maintain an Access database (the Access application is just a "user-friendly
front-end"). No need to have Access installed; the Jet Engine should be
installed with Office, in any case. You just need to set a Reference to a
Microsoft DAO object library. The Help files delivered with Access explain in
detail how to use DAO to create and manipulate the database and its objects.

I'm trying to use excel as a frontpage and foundation of my VBA application,
i cannot install Vb.NET or any other external application , i have to work
with XL.

i cannot install SQL server, not even access.

i tried using a very simple XML document as database, as i already know how
to generate and populate XML docs with VBA but it is not recommended since
it is not as optimized as a binary format, however i'm using this file
format to save my application's settings for every user as a simulated
personal profile.

then i tried using one XL sheet, but the file grew too much .

i'm gonna try Cindi's solution, i will create a DB4 from "Save as..." and
using the OLE db Provider insert and retrieve my values from this DB.

Cindy Meister
INTER-Solutions, Switzerland
http://homepage.swissonline.ch/cindymeister (last update Jun 17 2005)
http://www.word.mvps.org

This reply is posted in the Newsgroup; please post any follow question or reply
in the newsgroup and not by e-mail

 

[Peter Fallon]

Creating a custom data structure on disk and writing to it isn't the
problem (eg: the DBF format is well documented, or you can just write
out CSV files) - your problem is indexing (sorting), and the simple fact
that for anything except trivial amounts of data the VBA engine will be
woefully overtaxed and run like a dog doing somthing this complex itelf.

Are you interested in basic serialisation or soemthing more complex? You
won't find native VBA libraries for this simply because thats what
office users have Access for...

May I ask exactly what problem you have using a third party library? Is
it budget related (then I suggest looking for an open source or freeware
library) or some installation issue (ie: security) ??

 

[zz]

i have my hands tied because of security, some rules in out IT department
banned MDB as a reliable database format and instead proposed Oracle and SQL
server databases as standards.

now , Oracle i don't know how to use and SQL server just don't know how to
create those.


i could either require a database to be created and indexed from our IT
team, but since this program is sort of a proof of concept ,i want to keep
the most of secrecy
also i have no internet access at all, so i cannot donwload/install
absolutly nothing , unless it comes from microsoft .


i didn't tougth about writing to a csv file, good idea but i was thinking
if it is posible to save the csv file under a different extension, like
"MYdb" or something that links those files to my application.


my most sincere apreciation for your concern.

regards


--
---
zz [MX]
cuasi-musico,semi-poeta y loco

Creating a custom data structure on disk and writing to it isn't the
problem (eg: the DBF format is well documented, or you can just write
out CSV files) - your problem is indexing (sorting), and the simple fact
that for anything except trivial amounts of data the VBA engine will be
woefully overtaxed and run like a dog doing somthing this complex itelf.

Are you interested in basic serialisation or soemthing more complex? You
won't find native VBA libraries for this simply because thats what
office users have Access for...

May I ask exactly what problem you have using a third party library? Is
it budget related (then I suggest looking for an open source or freeware
library) or some installation issue (ie: security) ??

I was wondering if there's a posibility of generating a SQL, DBF, etc..
database from within a VBA application [more specifically excel's VBA].

the thing is that it would be incredible helpful for my purposes and i
cannot use MDB or an external /third party database generator.


i really, really would preciate any wire you can shoot on me about this.


sooo much thanks in advantage.