Posts by Mavyak

    Re: Use Variable As Table Name In Sql Server 2005 Stored Procedure

    I looked into the code you posted but I'm still in the same boat. I can query either way and get the table name into a variable. I need to use that variable in an SQL statement to determine the number of records in the table so I know if I can drop the table or not. Let's say I have variable @TableName. I need to be able to use that variable in an SQL statement that allows me to capture the results. Something along the lines of:


    SET @RecordCount = SELECT Count('a') FROM @TableName

    and then if @RecordCount = 0


    SET Sql = 'DROP TABLE ' + @TableName
    EXEC Sql

    FETCH NEXT . . . etc.

    Whenever I try to do that, I get a message along the lines of @TableName not being a table variable. But the only way I can see to use a table variable is if I create the temp table when I declare the table variable. I'm looking for something more along VB lines where I can declare a table variable and set it equal to an existing table. But I'm not sure that's possible in T-SQL.

    I receive backed-up MS SQL Server databases from clients. I then have to twist their data into our database. There are typically a large number of tables in the client's database with absolutely no data in them. I'm trying to create a stored procedure to drop the tables with no data in them so I don't have to waste time investigating them. The code below will simply print their names to the Messages window. If I can get the record count to print there as well then I can apply the same logic to dropping the tables. The error I'm getting when I parse the SQL below is:

    "Msg 1087, Level 15, State 2, Procedure NixEmptyTables, Line 18
    Must declare the table variable "@TableName"."

    Here's the SQL:

    Re: Hyperlink Failure In Access

    I think it should be something like "file://C:\Document and Settings\..."

    If that doesn't work, try replacing the spaces with "%20" (less the quotes). If that still doesn't work, then I'm at a loss. I've never dealt with that option before.

    Re: Sum Values For Each Month In A Access Query

    That is the query that gets you totals by month. I'm not sure how you are using the query's results or if it's being used as a recordsource for a form/report. It sounds like you have an existing query that is being used as a rcordsource for a form or report and you need monthly totals to display alongside an individuals record data. If that is the case, you will need to edit the query being used as a recordsource to include the fields you are lacking. Once that is done, the fields will be available for display on the form/report.

    All that, though, is a guess. Can you elaborate on the project?

    Re: Sum Values For Each Month In A Access Query

    Select Sum(<field containing values to add together>) As Total, DatePart("yyyy",<Date Field>) & "/" & Format(DatePart("m",<Date Field>),"00")
    From <your table here>
    Group By DatePart("yyyy",<Date Field>) & "/" & Format(DatePart("m",<Date Field>),"00")
    Order By DatePart("yyyy",<Date Field>) & "/" & Format(DatePart("m",<Date Field>),"00") Asc

    I think that ought to work. It's yyyy/mm format so the sorting will be chronologically ascending.

    Re: Copy Data To New Sheet With A Find Function

    Quote from StephenR

    If you apply that code to your example sheet it will work. At least it works for me.

    You are correct, sir. I don't know why it works, though. They way I interpret the command, it's just the first and last, but Excel looks at them as beginning and end points. I wouldn't have expected that. Learned somethin' today. Thanks!

    Re: Copy Data To New Sheet With A Find Function

    I think this line:

    1. For Each rng In .Range("A2", .Range("A2").End(xlDown))

    Might should have been this:

    1. For Each rng In .Range("A2:" & .Range("A65536").End(xlUp).AddressLocal(False, False))

    I could be wrong but I think the first way only looks at the first and last cell in column A. The second one looks at every cell that is populated in column A.

    Re: Export Data

    You could create two queries, one that exports all records with primary key less than (insert halfway point here) and another that selects all records with primary key greater or equal to (insert halfway point here). Export them to the same excel file and I believe they end up going into separate sheets. If your results are greater than 131072 results (65536 x 2) then apply the same logic but use three, four, or n queries.

    Re: Display Number Of Records Per Category In A Data Range

    Highlight the entire column and in the lower right hand corner it should tell you the number of populated cells. Subtract one from that value to account for the header row.

    If you don't see a count in the lower right hand corner, right click there and select "Count."

    Re: Identify Cause Of Long Running Function

    I think there's too much to edit to put up sample data. I really wanted to know what was taking so long so I could shorten the test phases of code execution. But I've got it down to waiting on the client for clarification on just three outstanding issues, which should only net nine or ten more code executions before the data gets fully extracted and moved to a database. I can live with five more minutes of waiting for columns to delete.

    Thanks for all your help on this. If the problem rears its head again I'll revisit it at that time.


    Re: Identify Cause Of Long Running Function

    I ran the code with the xlCalculationManual line uncommented once and it made no difference so I left it commented out. Unfortunately, I cannot send the spreadsheet. It's full of confidential HR data. My PC is brand new (I just got hired here and they set me up with a new computer). I'm running Vista now (need to update my profile but I still run XP at home). I've got a 2.3GHz processor and 2GB of RAM.

    Re: Identify Cause Of Long Running Function

    Quote from Sicarii

    The data that is left over and being it referencing to other files out on a network?

    If you perform the same operations manually does it go quicker?

    Do you absolutely have to delete the rows/columns?

    No time difference. I don't believe data is being externally referenced but have not confirmed. Spreadsheet comes from the client. I have to parse out the data in to about 15 other spreadsheets that get imported as tables into an Access database.

    Quote from Fencliff

    Do the operation in one go?

    This code saved me about 10 seconds (down to 22 seconds). While I'm appreciative of the code, twenty seconds still seems excessive to me.

    I'm going to have to investigate the external links further. However, I would think that the third commented out line of my code would have made more of a difference if the cause were links. I could be mis-interpreting what the command actually does, however.

    Thanks for your input. Keep it comin'!