October 24, 2012 at 10:00 pm
Front end MM Access adp file.
On SQL server 2005
Form bound but user must not change data directly
Unbound text box to input new data
Save button to add record.
Forms!some form!text box can't append...
Tried Me.textbox won't work...
Any advice?
October 25, 2012 at 4:59 pm
Hi
The brevity of your post is impressive but is a little limiting when it comes to providing you with proper advice. So posting the code behind the Save button would have made things a whole lot clearer and if my response is not helpful in solving your problem, then I suggest you actually post your code.
However, I think I get the gist of what your problem is so, ignoring the obvious question as to why you would want to torture yourself in this way 🙂 lets see what I can come up with.
To update the database manually the code behind the Save button will need to build and execute an SQL string. The simplest way to build you string is to simply use the Access Query function - but wait, you are using an ADP file (another obvious "why" question there) so I don't think that is going to work. You will need to include the control(s) from the form as the value(s) - me!ControlName should work unless the control is on a subform.
If you are doing record updates from this form, you will need to program both an append query and an update query (maybe use different buttons) - and then of course you might need to perform deletes in which case you will also need to build a delete button and appropriate code.
I hope this gets you started. If you have never created SQL strings in VB before, have a bit of a read and give it a go, but post back if you have trouble (post your code too) and I'll give you some tips on how to go about it.
Cheers
Rowan
October 25, 2012 at 8:59 pm
It is a brave ask...cause i searched all over and still cant come up with a solution.
I was thinking input parameters..but still havent got that to work fully yet.
I havent been able to create any codes for the button...
I do know a work around for the problem but it would be really great if I could find a way to do this still.
you seem to have the idea as to what i am trying to do. Its sort of like building a project in VB but in MS instead.
The thing is, how do i let the append stored procedure know that I am appending data FROM the form?
October 25, 2012 at 10:58 pm
Hi
The following code is an example of the Event Procedure assigned to the OnClick event of a button. It demonstrates how to create an SQL string and how to execute it. The pieces of text starting "me." are referencing controls on the form. If I was being fussy, I should be using "me!" to avoid any confusion in Access. This example is probably a bit more complex than you currently need as I am doing some stuff with some dates which are part of the record I'm inserting. I also have a loop for reasons which currently escape me (this is from a project I was working on a year ago).
It includes an example of both an insert and a delete - all you need now to complete the set is an update (and if you know SQL that shouldn't be too hard). But most importantly it provides you with an example of building an SQL string in VB. Positioning and inclusion of the double-quote characters (") are crucial as they not only mark the points at which the string is interrupted to add in the control values eg
& ",""" & Nz(Me.Brand, "") & """,""" &
but they are also used to surround string values.
The ampersand character (&) is the joiner but one of the most confusing things is the use of the double-quote. & """,""" & translates into the completed string as ",". Therefore & ",""" & Nz(Me.Brand, "") & """,""" & translates into ,"SomeBrandNameFromTheForm","
I suggest you do some research into using breakpoints and the Immediate Window (activated using <CTRL>G) if you don't already know how to use them. Its very handy to step through your code and to display the actual string being created.
The SetWarnings False command turns off the messages asking the user to confirm the action and SetWarnings True turns them back on again - a lot of new developers fail to turn them back on again and get into strife - you have been warned 😀
I'm using the NZ function because most of the values I'm inserting are strings so to avoid any issues I'm turning NULLs into empty strings (habit from coding Transact SQL).
Hmm! Re-reading your last past you mentioned stored procedures - are you wanting to use a stored procedure to do the data stuff? If so, it is possible to do it, but then I am used to using the Access MDB format rather than ADP so I'm a little vague as whether or not the methods I would use in an MDB will still work in an ADP. Lets tackle that when I have more info.
Please note, I live in New Zealand so our time zones are likely out of synch. I'm making this last post from my desk at work and then heading home - it is coming up for 6pm. It is also Friday 🙂 Wohooo!! TGIF!!! But I'll keep pace with this over the weekend when I get spaces.
All the best.
Cheers
Private Sub btnUpdateDataWarehouse_Click()
Dim dtEndOfMonth As Date
Dim dtLoopDate As Date
Dim strSQL As String
dtEndOfMonth = DateSerial(Year(Date), Month(Date), 0)
dtLoopDate = DateAdd("m", -12, dtEndOfMonth)
Do While dtLoopDate <= dtEndOfMonth
strSQL = "Insert Into dbo_rpt_ProgressiveStores ( " & _
"Store,[Site No],Brand,Region,Area,SiteDescription,SiteAddress, " & _
"SiteSuburb,[State],PostCode,TargetTonnes,DefaultDensity,ForTheMonthEnding) " & _
"Select " & _
"""" & Nz(Me.Store, "") & """," & Me.SiteNo & ",""" & Nz(Me.Brand, "") & """,""" & Nz(Me.Region, "") & """," & _
"" & Nz(Me.Area, "NULL") & ",""" & Nz(Me.SiteDescription, "") & """,""" & Nz(Me.SiteAddress, "") & """," & _
"""" & Nz(Me.SiteSuburb, "") & """,""" & Nz(Me.CState, "") & """,""" & Nz(Me.PostCode, "") & """," & _
Me.TargetTonnes & "," & Me.DefaultDensity & ",#" & dtLoopDate & "#;"
DoCmd.SetWarnings False
DoCmd.RunSQL strSQL
DoCmd.SetWarnings True
dtLoopDate = DateSerial(Year(DateAdd("m", 1, dtLoopDate)), Month(DateAdd("m", 1, dtLoopDate)) + 1, 0)
Loop
MsgBox "New Store data created.", vbOKOnly, "Add Store Data"
strSQL = "DELETE ProgressiveStores.*, ProgressiveStores.[Site No] " & _
"FROM ProgressiveStores " & _
"WHERE (((ProgressiveStores.[Site No]) = " & Me.SiteNo & "));"
DoCmd.SetWarnings False
DoCmd.RunSQL strSQL
DoCmd.SetWarnings True
Me.Form.Requery
End Sub
October 28, 2012 at 8:32 am
Okay,
after thinking long and hard as to how I will go about doing this, I decided that I will use ADP file to creat stuff, stored procedures especially and use ACCDB files as user front end.
I will also use web browsers to return reports standard to employees who wish to review stuff..
Got the layout well mapped out!
Also, using the ACCDb files gives me the option to connect to oracle!
Great bridge!
Thanks for the assistance. It still bothers me though that I was unable to achieve this. I hate it when that happens! Guess it happened for a reason 😉
October 28, 2012 at 11:14 pm
Hi
I think you have made the right choice - once you have the frontend developed you can compile it as well. Using an ADP forces you down a certain path, one which I have always found to create more work and maintenance hassles than it was worth. And you are right about the flexibility of the ACCDB with Oracle - ODBC might not be the fastest connection method but it is relatively simple to work with - but then I have been using ODBC for quite sometime now so I'm biased.
You can still utilise stored procedures in the ACCDB format - you just have to execute them via pass-through queries. A nice way to overcome some of the downsides to the way Access handles data. Passing parameters means you are forced to utilise VBA and querydef objects, but they are not difficult and there are plenty of examples around should you need a prompt. You can always post here if you have specific issues.
Using stored procedures via pass-throughs to handle database inserts/updates etc is a bit more work to build but possibly one of the better ways to use Access as a frontend. You have to weigh that sort of design against the standard Access approaches, but if you are working with a lot of unbound forms, I think the sp track is worth the effort. I'm sure a thread on that topic would draw a variety of opinions 🙂
All the best with your development. I hope I was able to help in some manner.
Regards
Rowan
October 31, 2012 at 4:51 pm
You have helped a lot actually. It was tru these discussions that i am talking to my self and realizing the best approach.
Anyways, i will continue to use this thread to post and I go along
so a few problems i encountered so far:
1. Bit field and boolean value ... work around, if you want to use a check box in an access form bound to a bit field in sql server...make sure that the bit field is set to yes/no.
2. all my data shows #deleted..fix...open linked table manager and refresh all tables...the connection was lost.
3. running my stored procedure...pass through queries work...but i dont think pass through queries like the DECLARE statement with the @ before the parameters...so what do you do..simple...do a pass through query with the statement being:
exec stored-procedure-name
set the ODBC Connect to the connection of the database.
if you are running an action query...set the "Return Records" property to No.
Yep, every day is one problem or another but at the end of the day, i learn...Thank goodness for google!
October 31, 2012 at 4:53 pm
i'm currently using a thread in the ms access section to post my findings as i go along. I am sure that there are others who experience these problems every day!
October 31, 2012 at 5:26 pm
problem: write conflict in ms access
solution...
make sure the table has a primary key
make sure no bit field has a null value
and ensure that the table has a timestamp field.
November 2, 2012 at 2:44 am
Sounds like the same advice you can find here: http://accessexperts.net/blog/2012/07/31/my-best-presentation-is-now-online-optimizing-access-with-sql-server/#comments
"i'm currently using a thread in the ms access section"
I can't see the thread, have you started it?
November 3, 2012 at 9:58 am
sdhanpaul (10/28/2012)
Okay,after thinking long and hard as to how I will go about doing this, I decided that I will use ADP file to creat stuff, stored procedures especially and use ACCDB files as user front end. ...
A bit late to chime in, but you might not be aware that ADP applications are not supported in the next version of Access - see http://technet.microsoft.com/en-us/library/cc178954(v=office.15).aspx for the details. And I agree with ProofOfLife about the issues with using ADP - we never use them.
Wendell
Colorful Colorado
You can't see the view if you don't climb the mountain!
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply