Excel w/ Access - Generic doubts

Good morning, guys!

I'm a beginner in VBA and I'm developing a system in Excel w / Access database that consistis of some queries. Why not only in Access? I need some Excel functions that Access does not have it (or at least I do not know about it). I have some questions regarding access to the database via ADO, that I could not elucidate.

So..

Do I have to open and close ADO connection and recordset objetcs for every access to DB?

Can I use the same variables as Public type in the Connection and Recordset for different parts of the system? Does not conflict on multiple queries and access to DB?

Is there any technical routine that I have to apply to avoid conflict when there are multiple access to the database?

grateful,

LF

Hi,

To be honest, if you're a beginner, this may not be the best project to start on, as there's a lot to take in. You are asking relevant questions though so it's a good start; the answer to each of your questions is.... it depends.

Do I have to open and close ADO connection and recordset objetcs for every access to DB?

Technically - no, you can open up a connection and use that connection for multiple queries and close it after they have all run. In practice, it might be better for you if you do however re-open and close the connection each time you need to query/update the database. It keeps the margin for error smaller, access doesn't really play nice in multi-user environments and you don't want users with multiple connections open at the same time from each machine because you've missed some error handling somewhere.

Can I use the same variables as Public type in the Connection and Recordset for different parts of the system? Does not conflict on multiple queries and access to DB?
Yes, but be careful with public variables, generally you should keep the scope of your variables to the smallest they can be and if you can do without any public variables, so much the better especially if you have a lot of different bits of code interacting with them. If you decide on public variables, you'll need to inspect their state before trying to use them in your code.

Is there any technical routine that I have to apply to avoid conflict when there are multiple access to the database?

It depends how many users you are going to have, more specifically concurrent users, access doesn't really scale well (though your code will dictate how well it scales), typically if your application is pretty intensive in terms or read/write, access will probably handle up to about 10 users. You'll know you have too many when you start experiencing problems - namely locking errors, you'll find that you can't update/edit records until all the users have rebooted their machines.

You'll also want to regularly back up and compress your database to help reduce corruption

I hope that helps

Kyle123,

I really appreciate your attention.

Actually I'm not completely lay. I've done some projects in VBA, though not expert, but nothing involving external database tools. I have a project where data is stored on sheets but it increased a lot and excel can't take it anymore. Then I need to make use of a database.

Another point is that the application is currently single-user and making use of a database I wish convert it to multi-user. I thought Ms Access would be effective in this task.

Given this scenario what would you recommend me?

Thanks,

LF83

Well access seems to be the way to go, how many users are you expecting to have?

Another database suggestion would be MS SQL Server Compact Edition (free)

About 15 or 20...

It depends how much writing/updating you do. I've had issues with that many users - so I'd suggest sql server but it isn't as intuitive as Access

Excel access SQL Server via ADO?

yes, the same as Access, though the sql is very slightly different

To me it doesn't makes much difference. I would have to study about MS Access, I can study SQL Server. It's better choose the best.

Not wanting to abuse, you could direct me to articles, manuals or even sample files about integrating Excel / SQL Server?

I really need Excel as a front-end.

Thank you,

LF83

Excel would be no different whether you choose Access or SQL server since you'd use ado.

here's the ms link: http://www.microsoft.com/sqlserver/e...s/express.aspx

Make sure to install the sql server management studio express as well

There'll be loads of tutorials out there, but it's mainly straight forward as long as you're comfortable with relational database structures

Kyle,

What about MYSQL? Does it work well with Excel?

It seems that SQL Server requires a good configuration to run. Maybe MySQL be a good alternative...

Thank you,

LF83

Mysql will work, but there'll be more online help for excel and SQL server rather than mySQL and excel