Using Access Query w/Variable

[cstimart]

I have an access 2007 database that has a handful of queries that are date range driven. Currently, I have to open the db and put the dates in a form...then the queries provide me the data for that data range.

Is it possible to use excel to connect to the access db and set those dates via a cell or variable in excel...therefore filling different tabs with the query data?

 

[xenou]

If you want to cut to the chase, just restate your original post. You said you want to update some kind of parameter? What parameter? Your original post is short on details.

 

[cstimart]

Are you working on SydneyGeek's tutorial or your original database? You are losing me going back and forth between these things.

I'd recommend go through the tutorial first. Learn how it works. Then apply the lesson to your database.

Re, the empty cells:
For i = 2 To Rw
rst.AddNew
For j = 1 To 15
If Len(Cells(i, j).Value) > 0 then
rst(Cells(1, j).Value) = Cells(i, j).Value
End If
Next j
rst.Update
Next i

OK...so I've worked with this code to solve the empty cell issue(s). Now the code crashes that the error says "You must enter a value in the ____________ field."

Looking at the access shell that was created, all of the fields are set to REQUIRE data. Is there a way to set the Field Required to "No"?

 

[xenou]

You really have to stop trying to do the tutorial and your actual database simultaneously.

The TUTORIAL may have fields set to "required" because it has provided data for all fields.
You should not create YOUR database with the code from the tutorial. YOUR database *already exists* !

 

[cstimart]

You really have to stop trying to do the tutorial and your actual database simultaneously.

The TUTORIAL may have fields set to "required" because it has provided data for all fields.
You should not create YOUR database with the code from the tutorial. YOUR database *already exists* !

Yes, I can modify so that I use an existing Access db, however, I do like the idea of creating a new/temporary db...this would be beneficial when someone else runs the macro and doesn't have access to the drive that an existing db would be on.

 

[xenou]

Does this have anything to do with your original question anymore? Your original post was not about creating databases with ADO and made no mention of the desire to do so.

I have an access 2007 database that has a handful of queries that are date range driven. Currently, I have to open the db and put the dates in a form...then the queries provide me the data for that data range.

Is it possible to use excel to connect to the access db and set those dates via a cell or variable in excel...therefore filling different tabs with the query data?

 

[cstimart]

Does this have anything to do with your original question anymore? Your original post was not about creating databases with ADO and made no mention of the desire to do so.

Being that I tried to create another post and was told to keep the related topic on one thread...it was subsequently closed/locked.

Please do not post the same question multiple times. All clarifications, follow-ups, and bumps should be posted back to the original thread. Per forum rules, posts of a duplicate nature will be locked or deleted (rule #9 here: http://www.mrexcel.com/forum/showthread.php?t=99490).

Therefore, I posted my follow-up question(s) in this original thread.

 

[xenou]

Allen Browne gives many examples about using ADOX here:
http://allenbrowne.com/func-adox.html

There is an example of a required field in his function CreateTableAdox()
.
Since this has nothing to do with setting a query parameter I'm completely lost as to where you are going with this. The other thread you mentioned was a duplicate question. That doesn't mean you need to ask all questions for the rest of your life in this thread - just don't ask the same question in two threads. Okay? To help in understanding why cross-posting is an issue (whether across forums or in separate threads in the same forum), see here:
http://www.excelguru.ca/node/7