Posts by RowanS

Re: Create Chart In Report From Query

Hi AL

In the rush of what was last week, I missed your post - my apologies. However, I'm glad that you have managed to resolve it and that I was able to help in some way.

All the best.

Regards

Rowan

Re: Where Do I Begin?

Hi khenzel

Firstly, let me say that it heartens me to see someone recognising the importance of understanding database design and theory. Because Access is relatively easy to use, people think that it is easy to develop database applications. Sadly, when the resulting apps fall over Access gets the blame when 99% of the time it is due to poor understanding of how these things actually work and an inappropriate use of Access as a data manager.

Well, to quote your post title, where to begin?

Probably enrolling in a database design paper at a local tertiary institute or night school would be the best way to go. There are plenty of books on database design theory and db management available so your local library would also be a source of information.

There are plenty of sources on the net too but the trick is finding one which guides you through an appropriate learning sequence, which is why I suggested enrolling in a course of some sort. I did a quick google on "database design theory" and came up with a few sites and I am sure there are more. The problem will be that a lot of them will be product specific although that can't really be avoided.

You need to understand:
(1) Conceptual modelling
(2) Logical modelling
(3) the concept and practice of Normalisation
(4) Physical database design (follows from conceptual and logical) which leads into understanding things like primary and foreign keys and data typing.

That might give you a bit of a head start.

Cheers

Rowan

Re: Create Chart In Report From Query

Awe, come on AL - you can come up with harder ones than this

Your selection syntax on your query is fine, but it is the way you are using the query (which accounts for it working when you run it from the query itself). In effect, because it is the data source for the sub-report, it is a subquery. Therefore, the hole selection should be on the master query. You use the ID to link the subreport to the master so do the initial selection at the top level - the list of IDs coming through from the master query will act as the filter for the subreport because of your link.

Simple job of cut/paste (from "WSTK_Details_Query" query to the "WSTK_Hole" query) and you'll be sorted.

In this situation a truer way to test the queries would have been to set up a third query linking the two "production" queries and running it from there ie mimic the report (sort of).

Let me know if this doesn't work for you or the above doesn't make sense.

All the best.

Rowan

Re: Create New Application With Access

Hi Praveen

Don't worry about the timing of your reply; I suspect we are in quite different time zones anyway.

The fact that Access is free and a database tools leads many down the wrong path in that they build apps using it and then enable a number of users to utilise those apps. Actually, it is not the building that is the issue but the use.

Access is simply not built with "multi-user" in mind. However, it is a great prototyping tool and if you find Excel forms easy to use, you shouldn't have much difficulty picking up Access - and of course you always have this forum

With a prototype built and working, the Access db can be upgraded into SQL Server - there is a tool for it and plenty of info available on this process both here and on the internet. The forms don't get wasted either as you have the choice of creating ODBC links to the SQL database or modifying the VBA to create and use OLE DB connections to the db (my preferred method). Access simply provides the front-end for the db - or you can simply take the designs and re-build in some other language. If you are considering going down this latter path let me know as I would offer some practical advice on the method of providing data to your Access forms.

My father, now retired and in his nineties, was a very good carpenter in his spare time. Amongst many of the things that I learned from him was "always choose the right tool for the job". This may mean a bit of extra time and effort getting to know how to use it, but it will always pay-off in the quality of the end result.

You seem to have a pretty good idea of what you are about so I am confident you will make the correct choices which fit your circumstances.

All the best.

Regards

Rowan

Re: Create New Application With Access

Hi Praveen

Just read your post and I will offer some advice based on my experience. I don't expect everyone to agree with it - but that's life and what makes it so interesting.

Firstly, lets deal with the database. If this application is to be multi-user in anyway, do not use MS Access. If it is cost driving you in this direction, there is still no justification as there are other alternatives. Besides, if this application relies on its data, then you owe it to yourself and your users to use the best database system you can - quality related to purpose is the most important consideration.

The choice of database is probably the most important decision you make in relation to this project. You cannot justify all the time and effort which goes into developing an application by basing it on an inferior database - it is too important. If that means spending some money, so be it - it will be worth it in the long-run.

Microsoft provide a free version of SQ Server 2005, and this would provide a solid base on which to develop an application. This will be fine for the development process, but when it comes to putting it into production, you may want to consider going down the route of a full software/hardware solution (mainly because this will make the management process so much easier).

There are a number of other free database servers available; Oracle have an older version available I think, and there is always MySQL, as well as others. However, I would choose MS SQL Server 2005 over MySQL every time; I am not going to justify that comment here as it is not the point of this post, but I do have very good reasons, from a developer standpoint, for making it.

Having provided your application with a solid database to sit on, we turn to the user interface. The decision here is important, but not so crucial, although an improper selection can cause time wastage, and in the very worst scenario, the complete failure of the project.

What drives your choice of development environment MUST be functionality - what it is you need your users to be able to do. I have a great deal of experience using MS Access as a front-end development tool for SQL Server databases and it has only let me down once, when the functionality required pushed beyond what Access was capable of handling. Specifically, the functionality required the incorporation of third party controls (software) which just did not work properly. If the functionality is relatively straightforward (I'd need to see a detailed spec to make any further comment on your app) Access would be fine if you are comfortable in that environment. Obviously, VB.Net offers a more advanced environment with the ability to develop web-based apps as well. At this stage your post does not make it clear why you are thinking of incorporating Excel.

However, as you are talking about the possiblity of a browser-based app, then you would need to look beyond Access I think. Using Access to build a LAN-based prototype might be a plan to follow as it is reasonably quick to get Access apps up and running (depending upon your experience both as a developer and with Access). So in terms of this style of app I would have to defer to the opinion of others, as most of my experience lies in desktop apps.

The only other advice I would give you is to plan your app properly before you even go anywhere near a database server or application development tool. If you are not familiar with SDLC (System Development Life-Cycle) concepts, then do some reading. Develop a proper spec which you clear with your users and when you develop, avoid large amounts of deviation from it unless you have really messed up in the spec. "Scope creep" is a development killer.

Also, create a model of the system as part of doing your spec - if this is done well, both you and your users will have a clear picture of what your goal is and this will make it much easier to achieve. So many developers make the mistake of missing this step because it seems like a waste of time - but I guarantee that it will save you development time in the long run.

Keep us posted on your decisions and progress.

All the best.

Regards

Rowan

Re: Access Equivalent To Personal.xls

Has anyone explored using Source Safe with Access objects?

I'd be interested in opinions/experience on this. I guess it also depends on how many programmers are involved but from my experience getting tangled up with object versions can happen to the best of us unless we adopt a disciplined approach to how we maintain our different projects.

Cheers

Re: Access Report Keep Togther

Hi

This may be a problem of data rather than report design specifically, although no doubt you will end up having to alter the design of the report to deal with it.

In relation to each study, examine the data for each looking for differnces. For example, are you trying to print a piece of data which is memo/large text in nature, and the amount of data is different between the studies? Is it possible you have a text field in your data which includes carriage return and/or newline characters embedded in the text?

If you haven't already, take a copy of the report for safe-keeping, then start pulling it apart. Start by simply removing any "Keep Together" settings that you might have - don't forget that these can be hiding in all sorts of places - on a control, on the section, subreport control, insdie the subreport, etc. Then start deleting bits from it to see if there is any effect. Test run it after each change, and don't change too much at once.

Another way to analyse what is going on is to build a brand new report using the same record source, but to keep it simple. Then just buikd it up again piece by piece, testing as you go. After a few years building Crystal reports, this is how I always approach building new reports - I have learned that this is preferable to building a whole report to completion before testing. Build each subreport seperately and test them as individual reports before adding them to the main report.

Maybe if none of this helps, you can post the report for us to have a look at.

Luck

Regards

Rowan

Re: Access Equivalent To Personal.xls

I have just been working with Access 2007 and it looks as though you can create temple MDB files. Earlier versions of Access don't provide this kind of feature directly as far as I am aware. However, if you are using an earlier version of Access, why not create an MDB with all your bits in it - efectively your template. Then each time you want to create an new app, just start with a named copy of this file.

Of course this doesn't help much when you are trying to replicate a change around each version. It would be possible to develop an Access-based function to copy an object from your current mdb into another. Perhaps someone has already developed such a tool - perhaps some research on Google might turn something up. Or maybe someone else who participates in these forums might have some bright ideas. I will watch this thread with interest as I am sure you are not the first to be asking such a question.

All the best.

Rowan

Re: Referencing Fields In Ado Recordset

Hi z

Yer, I can do that but I won't be in the office until tomorrow. I will sort out a sample for you.

Thanks for your interest.

Regards

Rowan[hr]*[/hr] Auto Merged Post;[dl]*[/dl]Hi z

When I came to set up a sample to post, I had some new thoughts about what it was I was attempting and have actually worked it out.

While this process was originally designed to convert a catalogue spreadsheet from a supplier into a format suitable for importing into the recipient application, it will actually be adaptable to other instances in which it is necessary to convert one spreadsheet into a fixed format. I will have to create an instance of this spreadsheet for each supplier's catalogue, but this is better than doing it by hand each time. For other types of data (purchase order lines and timesheet lines are on the agenda), I will simply change the format of the mapping worksheet - at least that is the theory.

A sample s/sheet is attached - the second worksheet defines the mapping of the columns and all the modules have been removed. Of course, this will only work with the current programming if there is a direct one to one relationship between columns in a data spreadsheet and columns in the final format. However, if some columns in the data spreadsheet are not required, they are simply not mapped and will be ignored. I forsee further modifications to handle some slightly more complex requirements (defining constant values and the columns into which they should be inserted; concatenation of data columns).

The conversion code which would need to be inserted into the spreadsheet, should anyone want to use/adapt it is as follows:

Public Function Convert_Data()
On Error GoTo Convert_Data_Err


    Dim conSSheet As New ADODB.Connection
    Dim rstSSheet As New ADODB.Recordset
    Dim strWorksheetName As String
    Dim recCount As Integer
    Dim fldCount As Integer
    Dim fldNum As Integer
    Dim colNum As Integer
    Dim colMappNum As Integer
    Dim wsImport As Worksheet
    Dim wsMapping As Worksheet
    Dim iRow As Integer
'Initialise variables
    strWorksheetName = Worksheets("Data_Mapping").Range("B6")
    iRow = 1
    fldCount = 0
    fldNum = 0
    colNum = 2
    colMappNum = 2
'Open connection to data sheet and read rows into recordset
    conSSheet.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & Application.GetOpenFilename & ";" & _
    "Extended Properties=""Excel 8.0;HDR=Yes"";"
    rstSSheet.Open "SELECT * FROM [" & strWorksheetName & "$];", conSSheet, adOpenStatic, adLockReadOnly
'Process the rows only if there are some to convert
    If rstSSheet.EOF <> True Then
        With rstSSheet
            recCount = .RecordCount
            fldCount = .Fields.Count
            Do
                Set wsImport = Worksheets("Converted")
                Set wsMapping = Worksheets("Data_Mapping")
'Loop through the recordset fields
                With wsImport
                    For fldNum = 0 To fldCount - 1
'For each field in the recordset, loop through the mapping columns to determine where to place
'the value, if the column in the data sheet is mapped.
                        For colMappNum = 2 To wsMapping.Range("B3") + 1
                            If rstSSheet.Fields.Item(fldNum).Name = wsMapping.Cells(4, colMappNum) Then
                                .Range(wsMapping.Cells(2, colMappNum) & iRow).Value = rstSSheet.Fields(fldNum)
                                colMappNum = 2
                                Exit For
                            End If
                        Next colMappNum
                    Next fldNum
'If SupplierPart not set in conversion, default to PartNo value - specific to catalogue conversions.
                    If Range("B" & iRow).Value = "" Then
                        Range("B" & iRow).Value = Range("A" & iRow).Value
                    End If
'If the final column has no value, enter a space character
                    If .Cells(iRow, wsMapping.Range("B3")) = "" Then
                        .Cells(iRow, wsMapping.Range("B3")) = " "
                    End If
                End With
                iRow = iRow + 1
                .MoveNext
            Loop Until .EOF = True
        End With
    End If
    
    With ActiveSheet
        .SaveAs Application.GetSaveAsFilename(fileFilter:="CSV Files (*.csv), *.csv"), FileFormat:=xlCSV
    End With
    
Convert_Data_Exit:
    On Error Resume Next
    rstSSheet.Close
    conSSheet.Close
    Set rstSSheet = Nothing
    Set conSSheet = Nothing
    Exit Function


Convert_Data_Err:
    MsgBox Err.Number & vbCrLf & Err.Description, vbCritical, "Error!"
    Resume Convert_Data_Exit
'Resume
End Function

A note to anyone wanting to look at the spreadsheet. I have worked on this using Excel 2007, but it has been saved as an 2002/2003 compliant xls file.

Thanks again for your interest in helping z.

Cheers

Rowan

Hi guys

I have a spreadsheet containing a parts catalogue. I want to import this data into an SQL db, but have to reorganise the data first. To do this, I have created a template spreadsheet which contains a form and code to open the external spreadsheet containing the data, read it into a recordset, write it into a worksheet in the template, and then perform a "Save As" to a CSV file. All works great!

This spreadsheet is a proof of concept and now I have to create a bunch of these that will cater for a different data file format as there are a number of suppliers who can provide catalogue data electronically. Also, there are a number of other imports which could be handled in the same way. My job is to get it all into the database, but I want to do this in a way that the imports can be done with as little programmer interference as possible.

One area for this is in the mapping of the data provided by suppliers into a standard format ready for importing into the database. In the sample I refer to above, the mapping is "hardwired" into the recordset code.

To make it possible for a "normal" user to create a new conversion template, my idea is to have a second worksheet in the template which defines the standard record format as required by the application doing the import, and the corresponding column in the supplier data which is to be read into it.

For example, my supplier data sheet contains three columns - Col A heading = Stockcode; Col B heading = Description; Col C heading = CostPerUnit.

My template for importing has a number of columns, some of which will be left blank as they are optional and corresponding data is not always included in the supplier's data. But there are four direct matches which must be populated - Col A and B in the template both correspond to Stockcode (one is for the PartNo in the table, the other for the SupplierPartNo - these can be different in practice); Col C corresponds to Description; and Col H which needs to be populated with the CostPerUnit.

My code, if I can remeber it correctly is something like:

'Code to connect to spreadsheet and create the recordset, then ....


do
     Range("A" & iRow) = rst!Stockcode
     Range("B" & iRow) = rst!Stockcode
     Range("C" & iRow) = rst!Description
     Range("H" & iRow) = rst!CostPerUnit
     iRow - iRow + 1
     rst.MoveNext
loop until rst.EOF = True

I want to replace the "rst!Stockcode", etc with references to the mapping contained on the second worksheet. The code I have attempted was something like:

'Code to connect to spreadsheet and create the recordset, then ....


dim wsMapping as worksheet
set wsMapping = "Col_Mapping"
with rst
do
     Range("A" & iRow) = wsMapping.Range("A3")
     Range("B" & iRow) = wsMapping.Range("B3")
     Range("C" & iRow) = wsMapping.Range("C3")
     Range("H" & iRow) = wsMapping.Range("H3")
     iRow - iRow + 1
     rst.MoveNext
loop until rst.EOF = True
end with

I have tried a number of variations on this theme but none have been successful - I get a message to the effect that the reference is not part of the recordset.

Does anyone know whether or not it is possible to achieve what I am attempting? If so, do you have any suggestions how I can correctly use the contents of the worksheet cells as the names for the data elements of the recordset. I hope I have explained this clearly enough.

Thanks

Rowan

Re: Combo Box To Run More Than One Sheet From Stored Proc Ss

Hi Tammy

Sorry, should have edited those out. They were used to get data which was passed to the stored proc as parameters. I have edited the "strSQL" variable and removed them to simplify the string.

Cheers

Rowan

Re: Combo Box To Run More Than One Sheet From Stored Proc Ss

Hi Tammy

Firstly, stop pulling out your hair or you'll look like me ~(:-{

Now, the following code snippet has been lifted from one of my spreadsheets.

Dim AConn As ADODB.Connection
Dim Ars As ADODB.Recordset
Dim sAConnStr As String
Dim strSQL As String
Dim dtSPStartDate As Date
Dim dtSPEndDate As Date


'Set the connection string
sAConnStr = "Provider=SQLOLEDB;Initial Catalog=DatabaseName; Data Source=SQLServerName; " & _
                    "Trusted_Connection=Yes;"


'create sql string for stored proc
strSQL = "exec SomeStoredProcName " & Sheets("Selection_Data").Range("C1").Value & ", " & _
    Sheets("Selection_Data").Range("D1").Value
    
'create and open the connection and recordset
Set AConn = New ADODB.Connection
Set Ars = New ADODB.Recordset
AConn.Open sAConnStr
Ars.Open strSQL, AConn, adOpenForwardOnly, adLockReadOnly

Try adapting this to your spreadsheet. Note that the parameters for the sp are held on one of the worksheets hidden in the workbook.

Hope this helps.

Regards

Rowan

Re: Automate Or Schedule Backups

Hi AL

I assume your question is related to backing up the data held inside the Access mdb, in which case GeorgS is right as the Access file has to be open. That is not to say that you couldn't develop a backup function in another Access mdb which performs a backup of your data held in a totally seperate mdb. There are even ways in which you can build timers but once again, the mdb holding the backup code has to be open permanently. This can be done but it is an awful waste of machine resource.

Probably the easiest thing to do is to use the backup function as GeorgS suggests or to simply copy the file to another location using the windows task scheduler.

Access is not like SQL Server which has its own Windows services and therefore always has a part of itself "alive".

Also note, if you are backing up the Access file, make sure that no-one has it open.

Cheers

Rowan

Re: Create A Number Sequence For Each Change In One Column

Hi

I think, before writing any code, we need to work out the steps the process will have to take.

If I am understanding your requirements correctly the first thing that will need to happen is that, for the current new record being processed, it will be necessary to check whether or not records exist for the department recorded in that new record.

If no records exist for that department, then we can allocate a "1" to the sequence and insert the record.

If records do exist for the department, we need to find the last sequence, add "1" ot it and then insert the record with this new number as its sequence.

Without building a test environment my first thoughts involve something like this:

declare @seqno_new_rec int
set @seqno_new_rec = case when (select max(seqno) from table1 
                                                 where deptno = @deptno_new_rec) = NULL
                then 1
                else (select max(seqno) from table1 where deptno = @deptno_new_rec) + 1
                end

The SQL CASE statement is like an IF ... THEN ... ELSE ... END IF function. I can't recall whether or not I have ever used a sub-query inside one before, but I don't see why it shouldn't work. The test for NULL should be replaced by a test for ZERO if the column is created in the table to have a default value of ZERO.

I hope this gives you an idea of where to go with this. For any of us to take this any further in terms of writing code for a trigger we would need some information on the table structure (a create table script is probably best) with some sample data.

Cheers

Rowan

Re: Not Showing Full Data In A Field

Hi

The solution is actually quite simple. My guess is that the data is descriptive data entered by users, and the application used to maintain it allows a user to use the <Enter> key. I have downloaded your file and checked the problem. As per my earlier post, the data contains the carriage return (Decimal value = 13; Hex value = 0D) and line feed (Decimal value = 10; Hex value = 0A) characters (result of using the <Enter> key).

You can ask the consultant to strip these out as part of the process of exporting the data. Alternatively, you can create an Access Query to massage the imported data. The following code can be pasted into an Access query and used to update the data as per your sample.

UPDATE Oracle SET Oracle.Field3 = Replace([Field3],Chr(13) & Chr(10),"",1,Len([Field3]));

If you are more comfortable in Excel, there is a Replace function available there as well I think.

Hope this sorts it for you.

Cheers

Rowan

Re: Pivot In Sql 2000

Hi

Try the link below:

http://searchsqlserver.techtar…rack=NL-464&ad=525294USCA

I have seen a number of articles on this topic as it is a reasonably common problem. I fhtis one doesn't help post back. I belong to a number of SQL Server groups and I'm sure I'll be able to find something more.

Cheers

Rowan

Re: Create A Number Sequence For Each Change In One Column

Hi

The sequence function in Oracle is there because Oracle does not provide an auto-increment data type. The sequence is created as a database object and a trigger is generally used to update the column on the table when a record is created.

In SQL Server you could use a trigger for your purpose ie when a new record is added, the trigger is activated. It checks for other instances of value "A" and uses max to find the last increment. Add one to it and update the column on the record concerned.

However, in reviewing what I have suggested above, I have this niggley suspicion that there is a more elegant way of doing this. I am not that fond of using triggers on SQL Server, while they seem to be a well accepted method in Oracle (basically as it lacks a lot of the added functionality of SQL Server) - just a personal thing.

If you only require the 1, 2 ,3 ... for ordering (say order of creation), then they don't need to be consecutive so you could add an integer column set as an identity. When you query the data you simply need to control the sort sequence. If you already have one of these as a primary key, then just combine the value and the key to give you your creation sequence.

Created date/time values are useful for this purpose as well and have the added bonus of telling you when the records are created as well as the sequence.

I hope the above helps with some ideas.

Regards

Rowan

Re: Not Showing Full Data In A Field

Hi

I haven't examined the file, but from GeorgS's response, it sounds as though you have Carriage Return or Form Feed characters in the data. You may need to strip out char(12) and char(13) from the text if you want it to display properly. If you are displaying this data as it stands on a form, you may want to consider using a memo data type rather than a simple text one.

Cheers

Rowan

Re: Button Error 2003 To 2007 Migration

Hi

What event are you using? 2007 may have changed the event processing. I only have 2003 installed so can't check for you. Maybe test some other events.

Sorry I can't offer something more specific - bit of a problem without 2007. If I can find someone with 2007 installed I will have a look and post again.

Good luck.

Regards

Rowan

Re: Locking Columns

Hi

There is no specific option in Access for locking columns; indeed, it is not an option on any database I work with with. This would have to be taken care of in the application itself. For instance, setting a control as locked so that data will be displayed on a form but not updatable. Updating data via events rather than making it available to users is another of controlling data ie using unbound forms.

But the biggest hole in Access which allows users to climb all over the data is the tables themselves. Forms are provided in Access to enable a programmer to control how data is added and modified. But if users have access to the tables, then the data integrity is potentially zero.

Which is why I advocate the use of Access runtimes on the desktops of users who don't require a full version of Access itself. Its free, eliminates the cost of Office Professional, and it is the ultimate way of protecting data, whether it is stored in Access itself, or whether it is held as links to a database server.

If you are looking at the locking of columns from the perspective of protecting the data, then I suggest you step back from the function you are currently designing, and look at the bigger picture of security and how it may be controlled through Access (or other means). My suggestion is that you keep it simple (but effective) and try to avoid the security options you have been looking at in regards to this post - while they do provide a security layer they are not a complete solution and in some respects can cause more problems than they solve.

All the best.

Regards

Rowan