UserForm loading on startup and using user input in SQL query 1

[WalterHeisenberg]

Hello,

First I am a beginner to VBA. I was asked to create an Excel workbook that when opened prompted the user for a date and executed a SQL query using that date as part of the where clause.

My first though was to create a macro to figure out how to get the SQL query linked up to a buttonclick sub. No problem there.

I have two issues, one, I can't get the form to load on startup! I tried adding sheet activate for userform.show() but that didn't do it. I didn't see anything on form properties. This must be simple.

Issue 2, I tried using the following syntax on the query:

 Destination:=Range("$A$1")).QueryTable.CommandText = Array("SELECT * FROM ""SAMINC"".""dbo"".""PAYMENTS" WHERE DATE = " & TEXTBOX1.TEXT & "")

I am getting a SQL syntax error. Thanks so much for help in advance! I appreciate it.

 

[WalterHeisenberg]

Sorry in my excitement that we might have this figured out, I forgot the one obvious problem. Select * works therefore the values of the fields itself is not the problem.

 

[SkipVought]

Each database simply represents a new company.

Why would you do that? It is not a Best & Accepted practice to chop your similar data up into different tables. Very counter-productive!

If you, at a minimum, had the companyID in a column and ALL your data in ONE TABLE, you would have a much better and useful structure.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[WalterHeisenberg]

I agree completely! Unfortunately this is an Accounting software that our company has purchased. Even worse, they do have a companyID column as part of this table!

 

[SkipVought]

Wow! Really? Interesting! What database application is this data being stored in, if I may ask?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[WalterHeisenberg]

Skip,

The data is on SQL server 2005. Right now I'm testing this macro on a computer that has SQL server installed locally. I will transfer this to client/server once I am able to get this running successfully. Thanks!

 

[SkipVought]

I might be inclined to load everything in one table and then write a procedure to create a new table for each CompanyID.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[WalterHeisenberg]

I could write a stored procedure and have Excel run it. I'll give that a shot. Thanks,

A

 

[WalterHeisenberg]

Thought I'd share the solution - I ended up creating a Stored Procedure that ran the query the way I wanted it and called it in the VBA macro rather than pass the query directly and it worked! All issues resolved.

Not sure why it works with the SP but fails if I pass the code directly. Thanks everyone for helping with this.

 

[SkipVought]

I never use stored procedures. I'd rather manipulate the SQL & Connect strings directly and submit to be executed. Just my druther.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}