vba odbc

查看Oracle客户端字符集：

 

     SELECT * FROM V$NLS_PARAMETERS;--返回当前用户环境中设置的字符集

　　 SELECT USERENV('language') FROM DUAL;--返回当前会话使用的字符集

 

特别说明：客户端的字符集要求与服务器一致，才能正确显示数据库的非Ascii字符，如果多个设置存在的时候，NLS作用优先级别：Sql function > alter session > 环境变量>注册表>参数文件> 数据库默认参数

 

     查看Oracle服务端字符集：

 

 

    SELECT * FROM NLS_DATABASE_PARAMETERS;--返回Oracle server端的字符集，来源于props$，是表示数据库的字符集

 

=====================================================================================

Sub oracle_conn()

Dim conn As ADODB.Connection

Dim rst As ADODB.Recordset

Dim connstr As String

Dim strSQL As String

Set conn = New ADODB.Connection

Set rst = New ADODB.Recordset

conn.ConnectionString = "DRIVER={Oracle in XE};DSN:Oracle_DSN;UID=system;PWD=034646"

MsgBox conn.ConnectionString

conn.Open

conn.Execute (strSQL = "insert into ROLE values ('2','abc');")

conn.Close

 

End Sub

=====================================================================================

IF NOT EXISTS (select * from dbo.sysobjects where xtype='U' and Name = 'T_Users')

BEGIN

  CREATE TABLE [dbo].[T_Users](userName varchar(20),userPass varchar(20));

END

方法二：

create procedure sel_tab

@table_name  varchar(20)

AS

  declare @flag int 

  set @flag=0;

  set @flag = (select flag from (select count(*) as flag  from dbo.sysobjects where xtype='U' and Name = 'T_Users') as sel_count);

  if @flag = 0

  create table T_Users(userName varchar(20),userPass varchar(20));

GO

EXEC sel_tab "T_Users"

=========================================================================

insert into [User] (UserId,Name,LoginName,Pwd)values(5,123,31321,1);

=========================================================================

if exists ( select name from sysobjects where name='表名' and type='U' )

--or--

if object_id('表名','U') is not null

drop table ...

create table ...

===========================================================================

IF EXISTS(SELECT * FROM sys.Tables WHERE name='test_table')

  DROP TABLE test_table

go

CREATE TABLE test_table(

  Test_ID int IDENTITY(1,1)  PRIMARY KEY  NOT NULL ,

  Test_Key varchar(10) NOT NULL ,

  Test_Value varchar(20) NOT NULL ,

  Test_Type int NOT NULL ,

  Test_BelongTo int,

  Test_Grade int DEFAULT 1,

  Test_Remark varchar(50),

  Test_Visible bit DEFAULT 1

);

go

==============================================================================

IF EXISTS(SELECT * FROM vba.Tables WHERE name='sales')

  DROP TABLE sales

go

CREATE TABLE sales(

  key  varchar(45) ,

  address varchar(100) ,

  latitude decimal(10,7),

  longtitude decimal(10,7) ,

  status varchar(100)

);

==============================================================================

SQL Server 2012・


MICROSOFT SQL SERVER 2012 ENTERPRISE CORE: FH666-Y346V-7XFQ3-V69JM-RHW28


MICROSOFT SQL SERVER 2012 BUSINESS INTELLIGENCE: HRV7T-DVTM4-V6XG8-P36T4-MRYT6


MICROSOFT SQL SERVER 2012 DEVELOPER: YQWTX-G8T4R-QW4XX-BVH62-GP68Y


MICROSOFT SQL SERVER 2012 ENTERPRISE SERVER/CAL EDITION: 748RB-X4T6B-MRM7V-RTVFF-CHC8H


MICROSOFT SQL SERVER 2012 STANDARD: YFC4R-BRRWB-TVP9Y-6WJQ9-MCJQ7


MICROSOFT SQL SERVER 2012 WEB: FB3W8-YRXDP-G8F8F-C46KG-Q998F




MICROSOFT SQL SERVER 2012 FC4R-BRRWB-TVP9Y-6WJQ9-MCJQ7