Zeo
asked on
Access ADP Project, SQL Table Creation Statements
I have a Microsoft Access(.adp) project with 16 tables in it. I'm running MSDE so that tables are in SQL server. I need to output the create statements for the tables and the attributes of each table, plus the primary key/foreign key relations how do I do it??
I need statements like this:
Create table firstcare_centers (
center_id decimal(4) not null,
center_name varchar(15),
pharmacy_id decimal(4) not null,
pharmacy_name varchar(50),
address varchar(60),
city varchar(15),
state varchar(4),
zip_code varchar(10),
phone_number varchar(13),
upsize_ts timestamp(2),
Primary Key(center_id));
Create table independent_contractors (
contractor_id decimal(4) not null,
first_name varchar(10) not null,
last_name varchar(20) not null,
pager decimal(13) not null,
speciality varchar(50) not null,
center_id decimal(4),
Primary Key(contractor_id),
Constraint fk_firstcare_centers_indep endent_con tractors foreign key(center_id)
References firstcare_centers ON DELETE cascade);
Create table center_physician_schedule
Contractor_schedule_id decimal(4),
Date date,
Start_time timestamp(2),
End_time timestamp(2),
Center_id decimal(4),
Contractor_id decimal(4),
Primary key(contractory_schedule_i d),
Constraint fk_center_physician_schedu le_indepen dent_contr actors foreign key(contractor_id)
References independent_contractors on delete cascade,
Constraint fk_center_physician_schedu le_firstca re_centers foreign key(center_id)
References firstcare_centers on delete cascade);
Create General_employee_informati on
Employee_id decimal(4),
Social_security_# varchar(50),
First_name varchar(10),
Middle_name varchar(20),
Last_name varchar(20),
Address varchar(60),
City varchar(15),
State varchar(4),
Zip_code varchar(10),
Phone_number varchar(13),
Employement_location varchar(10),
Center_id decimal(4),
Constraint fk_general_employee_inform ation_firs tcare_cent ers foreign key(center_id)
References firstcare_centers on delete cascade);
I need statements like this:
Create table firstcare_centers (
center_id decimal(4) not null,
center_name varchar(15),
pharmacy_id decimal(4) not null,
pharmacy_name varchar(50),
address varchar(60),
city varchar(15),
state varchar(4),
zip_code varchar(10),
phone_number varchar(13),
upsize_ts timestamp(2),
Primary Key(center_id));
Create table independent_contractors (
contractor_id decimal(4) not null,
first_name varchar(10) not null,
last_name varchar(20) not null,
pager decimal(13) not null,
speciality varchar(50) not null,
center_id decimal(4),
Primary Key(contractor_id),
Constraint fk_firstcare_centers_indep
References firstcare_centers ON DELETE cascade);
Create table center_physician_schedule
Contractor_schedule_id decimal(4),
Date date,
Start_time timestamp(2),
End_time timestamp(2),
Center_id decimal(4),
Contractor_id decimal(4),
Primary key(contractory_schedule_i
Constraint fk_center_physician_schedu
References independent_contractors on delete cascade,
Constraint fk_center_physician_schedu
References firstcare_centers on delete cascade);
Create General_employee_informati
Employee_id decimal(4),
Social_security_# varchar(50),
First_name varchar(10),
Middle_name varchar(20),
Last_name varchar(20),
Address varchar(60),
City varchar(15),
State varchar(4),
Zip_code varchar(10),
Phone_number varchar(13),
Employement_location varchar(10),
Center_id decimal(4),
Constraint fk_general_employee_inform
References firstcare_centers on delete cascade);
ASKER
Albert, I'm confused. I looked at that site but I'm not following SQLDMO.
What aren't you following?
This line: EXEC @hr = sp_OACreate 'SQLDMO.SQLServer', @object OUT
Creates the SQLDMO object on the server. SQLDMO is the object model that Enterprise manager uses to do things within SQL Server. This is also the programming extension programmers can use to access the same functionality in their programs.
Brett
This line: EXEC @hr = sp_OACreate 'SQLDMO.SQLServer', @object OUT
Creates the SQLDMO object on the server. SQLDMO is the object model that Enterprise manager uses to do things within SQL Server. This is also the programming extension programmers can use to access the same functionality in their programs.
Brett
ASKER
I'm really new to databases in general. I wrote out the create statements on my own with the primary keys, and foreign keys, but I was hoping to be able to do it directly from Access and my MSDE backend. I put the files I've created up on my website at http://www.techcacophony.net/firstcare/. I'm following an example laid out in Shcuams SQL case outlines. What I'd like to do it adapt the example from the book into MSDE.
I need really explicit steps. I have MSDE installed and I have the enterprise manager running, and I've got Visual Studio installed, so what tools do I need to use.
Many thanks and if I can get this foundation built, I'll award an additional 500 points for a grand total of 1000
I need really explicit steps. I have MSDE installed and I have the enterprise manager running, and I've got Visual Studio installed, so what tools do I need to use.
Many thanks and if I can get this foundation built, I'll award an additional 500 points for a grand total of 1000
"Many thanks and if I can get this foundation built, I'll award an additional 500 points for a grand total of 1000 "
There is a limit to 500 points per question....
I think the link I posted above explains pretty clearly how to create scripts using the SQLDMO. The scripts they have can be posted into Query Analyzer and ran. It will create stored procedures on your MSDE that you can call from your ADP project...
There is a limit to 500 points per question....
I think the link I posted above explains pretty clearly how to create scripts using the SQLDMO. The scripts they have can be posted into Query Analyzer and ran. It will create stored procedures on your MSDE that you can call from your ADP project...
ASKER
So just to be clear I need to be using VS.net to do this. I'm looking over at this walkthrough right now
http://www.sqljunkies.com/Tutorial/1BFBD444-DCB2-4318-A315-56BCA1D8C97D.scuk
right track, wrong track?
http://www.sqljunkies.com/Tutorial/1BFBD444-DCB2-4318-A315-56BCA1D8C97D.scuk
right track, wrong track?
"So just to be clear I need to be using VS.net to do this."
No, you just need to code it with something that can instantiate an object (VBA in access will work nicely).
No, you just need to code it with something that can instantiate an object (VBA in access will work nicely).
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Hi Brett
I helped him yesterday on another problem. And after reading the rest of the posts here he actually mentions EM. An yes I do have esp and your bank balance is ok, although you could be maximising your tax effective investments better.
I do not disagree with you that EM will create a point in time script. However generally this is what is required, and the question did not specify where point in time or dymanic creation is required.
Cheers Sash
I helped him yesterday on another problem. And after reading the rest of the posts here he actually mentions EM. An yes I do have esp and your bank balance is ok, although you could be maximising your tax effective investments better.
I do not disagree with you that EM will create a point in time script. However generally this is what is required, and the question did not specify where point in time or dymanic creation is required.
Cheers Sash
http://www.databasejournal.com/features/mssql/article.php/2205291