Search results

Simple. Once Excel is instantiated, create a sheet. Create a recordset that contains the data that are to be put on that sheet. Call the CopyFromRecordset function. Create the second sheet. destroy the first recordset. Create the second recordset. Call CopyFromRecordset again. Do this as many...

Why would you loop through a recordset when it's inefficient, resource-consuming and slow, instead of using a native Excel function that's fast and set-based?

Have you looked at the Excel CopyFromRecordset function? It is hyper fast, much, much faster than looping through a recordset. Just make your recordset(s) in VBA, instantiate Excel, and call the function.

Just build an Address table, with a one to many relationship between patient and address. Have a bit column for CurrentAddress. When they move, add their new address, and make it current. No need to copy any addresses this way, and one less table.

You may have a SQL Server DBA in your hospital's IT department. If so, that person could manage the SQL Server side of things, and create views and stored procedures for your app to consume. You'll get the best possible security if you go with SQL Server, too.

OK, I've looked at your tables. Here's how to do what I think you want. Create a query, called BaseQuery, with the following SQL: SELECT [Client Information].ID, [Client Information].Previous_ID, [Child Information].Previous_ParentID, [Child Information].[New _ParentID] FROM [Client...

What Bob Larson said about development time is right according to my experience. As to using update queries to deal with the hyperlink issue, that's OK too, but if you are on SQL Server you should instead use stored procedures instead of Access queries. Much more efficient, and they can execute...

Actually, as a RAD tool against SQL Server, Access is excellent. I usually can get a form built in minutes. If you take advantage of SQL Server views, stored procedures, and triggers, lots of functionality can be moved from the client to the server where it belongs. That said, the learning...

I just use Access as a front end, so I don't write any Access queries, all my SQL is in T-SQL on SQL Server, so I'm not the best person to help with the specifics of writing queries in Access. I would just write the SQL by hand, making the appropriate joins. If you upload your database, I can...

In SQL Server, the equivalent to an AUTONUMBER column is an Integer column with it's "Is Identity" property set to Yes. There is no hyperlink type in SQL Server. You could, however, store the URL as text in a column in your SQL Server table, and then assign it to the Hyperlink Address property...

I get this same problem. Only difference is that I am using SQL Server. Usually, deleting the record in the back end (not from Access) and then recreating it solves the problem. It's clearly not another user, it has to be some edit lock placed on the record in the back end and then not released...

I'd be curious to know the rationale for saying that. I mean, Jet still has take a look at the schem to process a SELECT *... SQL Statement, doesn't it?

Some other ideas: Never use "SELECT *....". Always select named fields. Also, select only the columns you need. Don't use an ORDER BY clause unless you have to. Don't loop through recordsets unless you have to. If you have to update a bunch of records and can do it set-based, just issue that...

I'm hosting my SQL Server on databasemart.com for 7 bucks a month. It's just the development DB, and we'll need to ramp to virtual servers later (so we can run scheduled SSIS packages), but for now it's cheap, and I can access the database both from my Access front end (I use linked tables) and...

Looks like a card reader app with lots of swipes. A couple of things spring to mind. SQL Server is fine with millions of rows, but you might want to consider splitting those data up into archival tables and a current table - if most of your querying will be current year or some subset like...

SELECT TOP 1 * FROM table WHERE table.DATETIME > " & "'" DateTimeOfCurrentRecord & """ & ORDER BY DATETIME DESC Will pull one record. You can tweak this if I have misunderstood the business problem.

You can do it in SQL by making a recordset in VBA and populating it with this SQL: SELECT COUNT(*) AS NumberOf Rows FROM Table1 WHERE Month = " & "'" & txtMonth & "'" AND Year = " & "'" & txtYear & "'" Then test if rs!NumberOfRows is 0 or not.

I agree with Lightwave. That's the easiest learning curve.

Glad I could help. If you run into trouble, I'm subscribed to this thread, so just post another reply here and I'll see it.

No need for that. I would make an updateable query for each subordinate table, and update it as a set, like "UPDATE qrySubordinate SET SubordinateRecord.ClientD = Client.ID" So qrySubrdinate would be updateable and contain the client.ID in each row.