Ready To Move On (Query Window)

[tfhwargt3]

I know SQL and I understand microsoft access pretty well. I have always written queries for Access for simply organizing data for other programs to use (mostly blackthorn). All I have ever needed to do was create a table manually using the table creation tool in Access and then manipulate that data using the query tool and then once there I would go into code view and write a query and save it for later use. This is great until you need to do multiple tasks or multiple queries inline.

For instance, I want to do a bunch of inserts based on IIF's and SELECTS on those inserts. For the time being I have been creating bunches of queries that create a bunch of temp tables in my access database (local computer) and then creating more queries to select from those tables, and then creating queries to drop the temp tables. Sounds retarded right... yeah that's what I think too!

So I am looking into stored procedures since they can run multiple queries inline, and I can store values in variables like real programming, but I don't really know where to start because for some reason none of these features will run within the query tool I am comfortable.

All of the online help seems to point to the fact that i will need a SQL Server running outside of my local computer to do these stored procedures. I am not really interested in setting up a server just to have all of my SQL data from MS Access in, so is there way to do this locally? If so, please point me to a tutorial or help guide so I don't take up any more of your time. I am sure I can easily learn how to do all of this if I am just pointed in the right direction.

Thanks in Advance.

 

[dhookom]

You have at least a couple options. Do "real programming" using VBA execute queries, maintain flow/steps, store values, etc.

The other alternative is SQL Server or SQL Server Express. SS Express 2005 is a free download and can be installed an run on most PCs. The SS Express 2005 has nearly all the same features of the full SQL Server with some limitations.

Duane
Hook'D on Access
MS Access MVP

 

[tfhwargt3]

I have decided I want to use VB or VBA to accomplish my task. Do I need ADO.NET or should I use some other version of interaction with the database? I don't need to create an application with buttons and reports in Access, I just need to manipulate the database with queries, but I want these queries to be conditional, and the easiest way for me to do this is use some form of VB to store variables and perform checks on them so I can execute these queries conditionally, as I mentioned.

I really just need a good tutorial to help me set up the beginnings of a database that I can run one simple query on from a programming interface so I can store these variables to check on. Then once I get that, I can import all of my data from my old databases and then code the necessary VB files to run my queries.

Let me know if you know a good tutorial for this. TIA

 

[dhookom]

I'm not aware of which tutorials or resources might be the best for your needs. I expect you should understand basic VBA coding as well as SQL. If you need to create recordsets then you have at least a couple options (DAO and ADO). DAO is the older technology that I prefer. ADO is nice since you can easily connect to SQL Server and many other types of data sources.

Duane
Hook'D on Access
MS Access MVP

 

[tfhwargt3]

Ok, so I have my database open in MS Access. If I click on "Tables" on the left I can see all of tables I have made using access, and "Query" will show me the same thing with Queries. I learned that you can write VB script right from access if you press Alt+F11 which will bring up the VB editor.

Now, this is where I am hoping to write code to directly affect/interact with database. My problem is, nothing I write in this window seems to be doing anything. Are there preprocessors or something I need to include in order to select my database or object(Table) I am planning to work on?

For instance, when I use PHP and MySQL for web stuff, I have to have a connect file that will connect and select the correct database before any real PHP code is executed.

Also, am I doing the correct thing to be creating a Macro by pressing F5 and then typing inside of the Sub() ... End Sub()

 

[tfhwargt3]

Also, I don't need any window to come up with buttons, i just want to run some of this VB code and then have it simply bring up the results much like the query window does in Access.

 

[dhookom]

You should click the Module tab and create a new module. Once you are in the code window, you can begin creating either Subs or Functions.

You need some interface to call your code. If you are in the module window, you can press Ctrl+G to open the debug window. This allows you to enter the name of your sub like

MySub strArg1, strArg2, intArg3, ...

If you code is called by a function, try:

? MyFunction( strArg1, strArg2, intArg3, ...)

You should compile your code (check the Debug menu) and use Option Explicit prior to any code.

Duane
Hook'D on Access
MS Access MVP