Replies

Nevermind, I worked it out. It wasn’t as hard as I initially thought it was.

Hi Paul,

Sorry to sound dumb but disable oplocks where? In access? In the file system? The NAS box?

The backend DB is in its own folder which is mapped to a drive letter for everyone.

Jim was right: IIf(GetUserInfo()<=7,([Rep]),GetUserInfo()) did the trick.

Here is a pic showing my code

 

Thanks for the input Wendell, I like the idea of tracking the version of components. It would be only for us 2 developers when we “commit” a new version (whole frontend db) to the server so that he doesn’t overwrite changes I have made.

I think I need code that checks the version of components between my copy and the master copy on the server and either imports and replaces newer objects from the server or at least lists newer objects so it can be manually done before my copy becomes the new master front end copy on the server.

So more details please 😀

It has an ethernet connection.

Will get new drivers

Thankyou Bob so much, most appreciated!!!!:clapping: I will study your code to try and understand where I was going wrong, I won’t just copy /paste, promise! 😀

I think the OP may also need to do more reading on the basics of how to use DAO and ADO in Access.

You are right, I do. I just don’t code continuously enough to keep what I know fresh in my head and sometimes, like this it just completely stumps me.:confused: This is part of an extensive application which has been built over many years with lots of commenting. Often I can look back to where I have used a process before and copy, paste, adapt with very little problem.

I like to build and test queries one step at a time, rather than trying to build the entire query at once.

Get it working with a test suite of variables you provide, before hooking it up to the application.

Refer back to the link in post #8 on using SQL statements in DAO and ADO.

I looked at the link and understand the examples given but I don’t seem to be able to translate that principle into getting what I had. –I am SOOOOO annoyed at myself —

I normally build the sql in a query and then copy and paste without any issue. In fact the sql works perfectly in a query but not as a recordset string. As I do this only sporadically as well as being mainly self taught I have hit the wall on this one. I have wasted too many hours already trying to modify the sql again hoping to fluke it a second time. I’m sure its in the sytnax of the sql somewhere but its just beyond me at the moment to understand what.

Hence my posting this little database. –Insert hair pulling and banging head against a wall emoticons here —

Sadly I fluked this but then went on to another event before saving it, closed everything in a hurry and so lost it and now can’t remember what I did. I know its in the strSQL expression.

I have prepared a sample database to show my problem. the form is to show the jobs assigned to an employee in the order they are to be done. It keeps throwing an error as you will see.

Items in the table are entered from a different job screen.

When you open the form you should be able to select an employee in the dropbox and have the listbox filled with items from the table set for them. I want to do it this way because I want to be able to use the up and down buttons on the side to set the order of the jobs and then click the save button to set it, by writing priority numbers back to the table for when it is loaded next time.

This allows new jobs to be prioritized as needed.

Thanks in advance to anyone willing to have a look

Do you have Dim rst As Recordset defined somewhere?

I also like to build SQL string variables like this…

strSQL = “SELECT tblAllocate.ID,
[INDENT][INDENT][INDENT]tblAllocate.JobID,
tblAllocate.Dept,
tblAllocate.Hours,
tblAllocate.Location,
tblAllocate.Comments ” [/INDENT] [/INDENT][/INDENT]
strSQL = strSQL & “FROM tblAllocate ”
strSQL = strSQL & “WHERE(((tblAllocate.Emp)=[Forms]![frmJobAllocationPage]![cboEmployees]) ”
strSQL = strSQL & “AND ((tblAllocate.Completed)=False)) ”
strSQL = strSQL & “ORDER BY tblAllocate.Priority WITH OWNERACCESS OPTION;”

Much easier to read for the person who has to maintain the code.

Access won’t let me do it. It keeps putting quotes after the first line

strSQL = “SELECT tblAllocate.ID,”

You can also try breaking up the statement:

strSQL = “SELECT tblAllocate.ID, tblAllocate.JobID, tblAllocate.Dept, tblAllocate.Hours, tblAllocate.Location, tblAllocate.Comments FROM tblAllocate ”
strSQL = strSQL & “WHERE (((tblAllocate.Emp)=[Forms]![frmJobAllocationPage]![cboEmployees]) AND ((tblAllocate.Completed)=False)) ”
strSQL = strSQL & “ORDER BY tblAllocate.Priority WITH OWNERACCESS OPTION;”

Thanks RG, that is a better way!

Debug.Print strSQL

OK I am getting the full strSQL string (must remember that)

strSQL=CurrentDB.QueryDefs(“qrySelect”).SQL

I tried this. If I copy and paste the sql into a new query the query runs just fine but when I call it using the above code I get an error 3061 (Too few parameters. Expected 1.)

I looked up this error and it may be that I need single quotes somewhere but I have no idea where.

It’s got me stuffed!

the underlying table has the following fields

ID:Autonumber
JobID:Number
Emp:Number
Dept:Text
Hours:Number
Comments:Memo
Priority:Number
Completed:Number
Location:Text

here is the code block in full

Code:

Private Sub cboEmployees_AfterUpdate()
Dim strSQL As String
Dim rst As Recordset

   On Error GoTo cboEmployees_AfterUpdate_Error

‘strSQL = “SELECT tblAllocate.ID, tblAllocate.JobID, tblAllocate.Dept, tblAllocate.Hours, tblAllocate.Location, tblAllocate.Comments FROM tblAllocate ”
‘strSQL = strSQL & “WHERE (((tblAllocate.Emp)= [Forms]![frmJobAllocationPage]![cboEmployees]) AND ((tblAllocate.Completed)=False)) ”
‘strSQL = strSQL & “ORDER BY tblAllocate.Priority WITH OWNERACCESS OPTION;”

Debug.Print strSQL; qryAllocated
strSQL = CurrentDb.QueryDefs(“qryAllocated”).sql

Set rst = CurrentDb.OpenRecordset(strSQL)
    With rst
       .MoveFirst
       Do Until rst.EOF
       lstJobs.AddItem tblAllocate.JobID & “;” & tblAllocate.Dept & “;” & tblAllocate.Hours & “;” & tblAllocate.Location
       .MoveNext
       Loop
    End With

   On Error GoTo 0
   Exit Sub

cboEmployees_AfterUpdate_Error:

    MsgBox “Error ” & Err.Number & ” (” & Err.DESCRIPTION & “) in procedure cboEmployees_AfterUpdate of VBA Document Form_frmJobAllocationPage”

End Sub

Thanks for your continued input. I have been a reader of this forum for almost as long as I have been building/maintaining this database and came across the frontend/backend concept early and have been running like this for many years. I used code I found along with version numbering to keep my frontends current. Each time I commit a change the version number is updated in the backend and my frontend and my frontend is copied to the NAS box. It also writes a copy of the old frontend to a storage folder in case I need it. Each frontend checks its version number against the backend on startup and if needed, writes and launches a bat file which closes the frontend, copies a new frontend version in from the NAS box and then launches it and then deletes itself on completion. (thanks to Renaud – http://blog.nkadesign.com/microsoft-access/)

I currently have backup software that copies the backend database to usb media (1 for each weekday) as well as being copied to dropbox each night (its only 86MB in size) and a portable HD once a week, so I am well into the routine of that.

One of the reasons this database exists was because we couldn’t find software that did what we wanted. The manufacturing software we looked at seemed to be geared towards large scale production and inventory where most of our stuff is one off and material is bought per job. In most cases, 6 off is a major run for us.

When I first started we were part of a larger company who used us as a cash cow. They kept raking the money out while putting only the bare minimum back in. They did introduce computers with Office 97 with Access and the 1 day access training showed me the answer to the frustrations I was having with Excel. The foreman was willing to let me have time to see what I could contrive and it went from there.

Keeping track of hours was our first reason. Being access, customer job history then just became available, then delivery dockets were added, then quote forms using a shared items table and then QODBC to give us read access to Quickbooks to be able to pull purchase orders and costings for a job and then many little tweaks along the way to create something that suits us and how we work.

Question: Wendel, do I really need dedicated server software to run SQL Server Express on? (it runs on win7) I realise I need a server machine but as all our web hosting and email is done via our IP provider and that the Backend is the only shared file we have do I need server software?

From reading elsewhere, the fact that sql server does things there and then feeds you the answers rather than grabbing the whole table and then filtering it certainly sounds like there are definite advantages. I will certainly start looking into the workings of SQL Server, I’ve even come across an Australian SQL Usergroup forum. So, onward and upward!

Thanks for your reply. After a thorough readthru of your post I agree that option 1 is the best way to go and what I had in mind.

Considering the info given, would SQL Server Express be enough for my needs?

As for server software, would either of these be suitable:

Microsoft Server 2008 Foundation 64bit R2 ($99)
Microsoft Server Essentials 2012 R2 ($595)

or would I need

Microsoft Windows Server Standard 2012 R2 64bit ($995)

My local store has this on offer as a starter.

ASUS RS300-E7/PS4 Intel Server ($1995)

I’ve seen many, many user-developed databases start up. Usually the person (and the skill set) depart in time, and the database loses it’s prime mover.

This comment truly reflects our situation and while (all going well) I expect to work here until retirement (15yrs) I would like to futureproof as much as possible. We are an engineering workshop that has benefited greatly from my interest and assisted (thanks guys) ability to grow the database from its initial premise to the wide ranging tool it has become and I would hate to see it fall over because I was gone.

Thanks Wendel for your insight.

My situation is: This is part of my Job card procedure.
A card is raised and items are added to the job using a subform and items table. In a perfect world the user may decide to create a multicard job and so will set an option and enter the first item as a summary line and then the items. What happens is that eg a gearbox comes in and a card is raised to book strip and inspect to, the job is quoted and items of work are added to the job. Depending on the scope, it may turn into a multi card job but may not. If it is, my multicard summary item needs to be the first item.

I realise that any new record will be written to the bottom of the table so this is what I am looking at now after some more browsing.

Write the existing recordset for the subform to a temporary table then add a new record to the underlying table and refresh the recordset.
Open the temp table and .movelast
Open recordset and .movelast
write field data from temp to recordset
.moveprevious on both tables
repeat until reach top record of temp table
.movefirst on recordset and clear fields

top record is now clear for summary

I hope to use the following combined code from examples off the net (adapted)

Code:

Private Sub TestTemp() 
   On Error GoTo ErrorHandler
   Dim strSQL as String
   Dim strTable as String
   Dim RSC as recordset
   Dim RS as recordset

   strTable = “tblTempTest”
   ‘Delete the table if it exists
   DoCmd.DeleteObject  acTable, strTable
strSQL = “Select * INTO ” & strTable & ” FROM tblCustomers ” & _   <<<< How do I write the SQL to be from me.sbfItems.Form.Recordset
"Where CustomerState = 'ILL'"
Currentdb.Execute strSQL

Add a record to the underlying table and requery

set RS = me.sbfItems.Form.Recordset
Set RSC = currentdb.Openrecordset("tblTempTest)
RS.MoveLast
RSC.MoveLast
While Not RSC.BOF
RS.edit
RS![Field1]=RSC![Field1]
RS![Field2]=RSC![Field2]
etc.
RS.Update
RS.MovePrevious
RSC.MovePrevious
WEnd
RS.Close
RSC.Close

Exit Sub
ErrorHandler:
IF Err.Number = 7874 Then
Resume Next 'Tried to delete a non-existing table, resume
End If
End Sub

Is this feasible and/or advisable?

Regards,

Allan

I discussed this with my ISP and they are providing a replacement wifi-modem-router, hopefully this solves the problem.

OK, the wired network connects 3 computers, nas box, ethernet printer, tv, 2 game consoles and the extender box to the modem/router via a 16 port smart switch. The wireless router/modem and the extender use the same wireless channel and SSID and the laptops, smartphones and ipad connect to whichever signal is closest.

So, the printer is connected to the wired network and devices connecting to the network via the wireless extender can access the printer but devices connecting to the network via the wireless router/modem cannot.

It feels as if the modem/router wireless only allows internet connection but not LAN connection.