Altering SQL Linked table from VBA (1 Viewer)

Thicko

Registered User.
Local time
Today, 14:50
Joined
Oct 21, 2011
Messages
61
Hi All,


Recently upsized Access Db to SQL Server.



One issue we have is if somebody added a new drug to the access database it creates a new column in the Access table.


Code:
CurrentDb.Execute ("ALTER TABLE tblDrugListOral ADD COLUMN " & DrugNameWithOutSpace & " Number;")
Problem is now the table is linked to SQL Server this won't work.


Had a read around and seems should only change SQL from back end, but each time a new drug needs to be added I don't want this to have to be done manually. Big risk, turnaround time etc..


Is there a way to update back end SQL table from front end Access VBA?


Any help appreciated.
Many Thanks
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 07:50
Joined
Aug 30, 2003
Messages
36,118
Your problem is not the inability to add fields for new drugs, but the need to. ;)

It doesn't sound like your table is normalized. Normally a new "item" would be a record in a table, not a field. Selling a new drug (if that's what you're doing) should not require design changes to the database. What do these fields represent? What is your table structure?
 

Uncle Gizmo

Nifty Access Guy
Staff member
Local time
Today, 14:50
Joined
Jul 9, 2003
Messages
16,244
if somebody added a new drug to the access database it creates a new column in the Access table.

Is there a way to update back end SQL table from front end Access VBA?

As Paul says, it's a design issue.

You should never have a situation where a new field needs adding to your database because of something a user needs to enter.

The problem arises from the misconception that MS Access is just a more powerful version of Excel.

I have blogged about it here:-

http://www.niftyaccess.com/excel-in-access/

I also provide a free tool to help you rearrange the structure of your database. If you need it, email me and I will send you instructions on how to get it for free.

Sent from my SM-G925F using Tapatalk
 

SQL_Hell

SQL Server DBA
Local time
Today, 14:50
Joined
Dec 4, 2003
Messages
1,360
As others have said, this looks to be bad design and things like this should be normalised off to a new table or the drug should be a new record in a reference table.

I think your example is failing because you seem be adding a new field of data type "number" which doesn't exist in SQL server, perhaps you mean INT or DECIMAL?
 

Thicko

Registered User.
Local time
Today, 14:50
Joined
Oct 21, 2011
Messages
61
I do get the idea this isn't great design, and think early building in ignorance was a problem. To give a bit more background the system is for chemotherapy production worksheets.

Each drug has a dose range that that corresponds to number of vials required. Enter the dose and out pops a worksheet with number of the right drug vials required, + syringes + needles etc..

Because of the huge range of vial sizes cant use x3mg / x5mg / x20mg. (0.44mg --> 1400mg)

There's also an issue that some vial sizes of particular drugs can only be used for a particular administration route.

Restructuring is soo much work to all the code that runs of this I'd love an alternative solution but I'm thinking I should just get started.

DrugName / DrugRoute / MinDose / MaxDose / DrugA100mg / DrugA20mg
Drug A........Injection........0.............20.................................1
Drug A........Injection........20...........40.................................2
Drug A........Injection........40...........100............1.....................
 

Users who are viewing this thread

Top Bottom