Excel VBA ODBC连接导入列格式

I'm looking for a way to reformat some fields that are being imported to my sheet using an ODBC connection. The fields in particular are the date fields. I need to import these as text or some other format because the data in the database will sometimes have dates of "1850-01-01" or "0001-01-01". When importing into excel, the dates just show as #### signs.

我正在寻找一种方法来重新格式化一些使用ODBC连接导入到表中的字段。特别是日期字段。我需要将它们导入文本或其他格式，因为数据库中的数据有时会有“185001 -01”或“0001-01-01”的日期。当导入excel时，日期显示为###符号。

Here is the edited query I'm currently using:

下面是我正在使用的编辑过的查询:

Public Sub REFRESH_DATA()

Dim cnDB As New ADODB.Connection    'Declare the connection object.
Dim rsRecords As New ADODB.Recordset 'Declare a Recordset object.

'Open the connection

cnDB.Open "DSN=DB;Database=DB;Servername=server.net;UID=username;Password=password;Port=0000;ReadOnly=0;SQLBitOneZero=0;LegacySQLTables=0;NumericAsChar=0;ShowSystemTables=0;LoginTimeout=0;QueryTimeout=0;DateFormat=1;SecurityLevel=onlySecured;CaCertFile="

rsRecords.Open "SELECT REGION_CD, CUST_NO, EFF_DATE FROM DATABASE.TABLE", cnDB

'Print the records in the correct table
.Range("A2").CopyFromRecordset rsRecords

'Close everything
rsRecords.Close
Set rsRecords = Nothing
cnDB.Close
Set cnDB = Nothing

End Sub

The column EFF_DATE is the column in question.

EFF_DATE列是有问题的列。

1 个解决方案

#1

Posting the answer to my own question for future posterity:

将问题的答案寄给未来的后代:

Where my SELECT statement is EFF_DATE, I changed it to read CAST(EFF_DATE as varchar(30)) which then maintains the format from the database itself.

在我的SELECT语句是EFF_DATE的地方，我将它改为read CAST(EFF_DATE as varchar(30))，然后从数据库本身保留格式。

#1