Add New Field from Access to SQL backend

L

LadyCES

I have a client with an access 2002 frontend linked to a sql backend. I need
to add a new field to the sql backend from access. I'm new to sql backends
and work offsite so I don't have access to their sql server. I am tring to
set up a wrkodbc.connection but am not sure of the commands to then add a new
boolean field or if I even can. Thanks,

Christine
 
R

Ron Weiner

Of course you realize that Sql Server does not have a Boolean column data
type, you will have to use a Sql Bit column type. In order to create the
new column you will have to connect to the Sql database with privileges that
permit changing the database structure and then run a DDL query that adds
the field to the table. The query might look something like this:

ALTER TABLE [YourTable] ADD
[YourNewColumn] [bit] NOT NULL CONSTRAINT [DF_YourTable_YourNewColumn]
DEFAULT (0)

Code might look like:

Set cn = New ADODB.Connection
strCon = "Valid connection string to your servers " _
& "database with appropriate privileges"
strSql = "The example sql DDL above"
cn.ConnectionString = strCon
cn.Open
cn.Execute strSql
cn.Close
Set cn = Nothing
 
L

LadyCES

Thanks Ron, I'll give it a try. I did learn about the lack of boolean in sql
and knew it had to be a bit. Thanks again. Chris

Ron Weiner said:
Of course you realize that Sql Server does not have a Boolean column data
type, you will have to use a Sql Bit column type. In order to create the
new column you will have to connect to the Sql database with privileges that
permit changing the database structure and then run a DDL query that adds
the field to the table. The query might look something like this:

ALTER TABLE [YourTable] ADD
[YourNewColumn] [bit] NOT NULL CONSTRAINT [DF_YourTable_YourNewColumn]
DEFAULT (0)

Code might look like:

Set cn = New ADODB.Connection
strCon = "Valid connection string to your servers " _
& "database with appropriate privileges"
strSql = "The example sql DDL above"
cn.ConnectionString = strCon
cn.Open
cn.Execute strSql
cn.Close
Set cn = Nothing

--
Ron W
www.WorksRite.com
LadyCES said:
I have a client with an access 2002 frontend linked to a sql backend. I need
to add a new field to the sql backend from access. I'm new to sql backends
and work offsite so I don't have access to their sql server. I am tring to
set up a wrkodbc.connection but am not sure of the commands to then add a new
boolean field or if I even can. Thanks,

Christine
Click to expand...
Click to expand...
 
Top