Use Excel as Database by opening excel workbook as ADODB object

[dragonvoice]

Hi,

I have seen very limited help online on this because most people are trying to use other sources as either front end or back end, such as Access, SQL server or .NET etc. But in this case my database sits in a in-house analysis application in Excel, I have no support in any corporate database nor do I have Access application in my standard XP installation. So I need to use Excel as a database to store data.

I have created a connection that queries tables fine, so that part worked well. Here's the code

Please Login or Register  to view this content.

This part was working fine.

However the problem is I need it to have other basic function of a datbase, such as adding, editing and deleting tables.

But here's where the probelm lies, it doesn't work no matter what form of SQL or code combinations I tried. It always gives me an run time error '-2147217887' (8004e21) <- some random long strings that are different every single time.


The code is as below:

v1:

Please Login or Register  to view this content.

This one didnt work.

v2:

Please Login or Register  to view this content.

This one didnt work either.

[JosephP]

you should be able to use INSERT INTO queries to append data or SELECT INTO to create new sheets http://support.microsoft.com/kb/295646

[Kyle123]

Can't you install SQL Server Express? It's free

[dragonvoice]

you should be able to use INSERT INTO queries to append data or SELECT INTO to create new sheets http://support.microsoft.com/kb/295646

Hi,

thanks for your response!

I tried the post you have given me and it still didn't work...

Here's the code:

Please Login or Register  to view this content.

[JosephP]

try it with a closed workbook-you should not use ado on an open one