Search results

Yikes. I now have this same problem. We sell software to pro sports teams, and the trend now is for them to issue iPads to their players. Our app is Access 2010 front end, SQL Server 2008 R2 back end. Most of the important business logic is encapsulated in server-side views and stored...

Yes, SQL Server locking trumps in this case. One approach would be to use stored procedures, but only for those INSERTS or UPDATES where locking is an issue. You would want to use BEGIN TRANSACTION and COMMIT TRANSACTION in your sp. For tables that you don't have locking issues with, just let...

Gemma is right. If your SQL Server and app are not set up right, by that I mean the correct locking level for a particular table, the one thing I have seen is blocking, which happens at the SQL Server level. If you have a SQL Server DBA on your team that's his problem, and he'll know how to...

I would import the file into a temporary table, and then parse it out. Since there are 8 million rows, I'd argue for a move to SQL Server. This kind of recurring import cries out to be done in SQL Server Integration Services (SSIS) which was designed for importing large amounts of data in...

Since you are tracking mood, I'm guessing that the patients are in talk therapy to treat various forms of depression. I know that therapists generally just want two data points - date, and mood. Mood being measured from 0 to 10. 0 being suicidal or already killed self, 10 being best mood...

You could bring the file in to a staging table, and then select from that table each business object (CH, SH, PD, etc.) from that table to import them into their appropriate table(s). That way you a lot of SQL to populate the real destination tables, but that SQL is simple.

I just built some security stuff that used the Access system table MSysNameMap. You might look into building a link table that has the human-understandable form name that uses the actual form name from MSysNameMap to pass to the code that opens the form. I'm solving a different problem...

Dim p1 As New ADODB.Parameter Dim sSQL As String Dim rs As New ADODB.Recordset Dim rs1 As DAO.Recordset On Error Resume Next Set cmd = New ADODB.Command InitConnection cmd.ActiveConnection = cn p1.Direction = adParamInput p1.Type = adInteger p1.value =...

I didn't see this post or I'd have replied earlier. No way to bind a form to a stored procedures. You could use a stored procedure to populate a recordset and go fromn there. I'll let the Acccess experts advise you on that. Here's code I wrote to execute a parameterized stored procedure. This...

SQL Server doesn't have a limit on tables or other objects. SQL Express is free, but lacks some things Standard and Enterprise have, such as scheduled jobs. Since Access has none of the things that Express is missing, it should not cause a problem. I've used the upsizing wizard in Access to...

It isn't so much qualifying - the federal government doesn't approve or disapprove technologies or strategies for safeguarding unclassified information, they just impose penalties if you fail to protect the information under HIPAA. When I was working with a US health insurance company, we used...

In the USA you have HIPAA, so storing any medical information in an unsecured Access DB is not a good idea.

I agree. If you need real security, move to one of these databases.

I second this approach.

My SQL Server is in Texas in the USA. My users are all over the world. I'm in a different part of the USA doing development. My solution was to buy SQL Server hosting with one of the many companies that offer it, and make my Access app aware of the correct connection string for the SQL Server...

maybe also look into correlated subqueries, if the number of correlated rows is known.

You should drop firstname and lastname from the ParticipantAttendence table. You've properly linked it with the ParticipantID. Not the question you asked, though.

That's how I learned it, from a commercial app we bought. You are perfectly positioned to become a SQL Server DBA. It's a good thing to be. There's probably a SQL Server users group if you are in a city. Having colleagues to bounce things off is a big advantage, especially in the beginning...

Galaxion: I've used SSIS on one huge project, and the learning curve is steep. You build SSIS packages in Visual Studio, and it's such a rich environment that it's daunting at first. It was designed for huge IT shops where there are dedicated SSIS developers. SSIS imports text files...

All these ideas will work, and a parameterized stored procedure is the best way, I think. Better than that, however, is to pull the data out of the Access DB using either a Data Transformation Services (DTS) or a SQL Server Integration Services (SSIS) package, depending on your version of SQL...