SQL "numeric" type -> displays as 〱&

lenny3k

New Member
Joined
Jan 15, 2003
Messages
18
Hi there.

I have a simple Excel workbook with a VB Macro that attempts to display the contents of a SQL Server database table.

The method used to display the data in the Macro is "CopyFromRecordset".

One of the fields to display is of type "numeric(18, 0)" in the SQL Server table. For some reason, the data contained in this column is displayed as 〱〰㐱〷〰 when it gets as far as the worksheet.

The actual values in the database are along the lines of 1000401000, 1000401001 (so they're not bigger than the "integer" SQL datatype would allow).

NB: This doesnt seem to be a cell formatting issue - the data in the worksheet cells is literally 〱〰㐱〷〰 - it can't be reformatted to display as anything else. Also, the values in the database (1000401000 etc) can be copied and pasted into the worksheet manually with no problems.

Any ideas?

Any help would be appreciated!
 

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.

Richard Schollar

MrExcel MVP
Joined
Apr 19, 2005
Messages
23,707
Hi

Please post the full macro code you are using and also indicate what references in the VBE Tools>References dialog you have checked. Thanks.

Richard
 

lenny3k

New Member
Joined
Jan 15, 2003
Messages
18
The important code is printed below. Note: this is an old Macro so it's possible it just needs to be updated to use newer technologies - the problem is arising with Windows/MSOffice 2003 - it may not have been a problem previously with NT (although I cant be sure about this).

References include:
MS Forms 2.0
MS Office 11.0
OLE Automation
MS Excel 11.0
MS DAO 3.6 Object Library


Code:
    'Database Connectivity
    Dim con As Connection
    Dim wsp As Workspace
    Dim rst As Recordset
    Dim qry As QueryDef
    Dim ReportQuery As String
    

    'Create Workspace and open connection
    Set wsp = CreateWorkspace("", "admin", "", dbUseODBC)
    wsp.DefaultCursorDriver = dbUseODBCCursor
    Set con = wsp.OpenConnection("", dbDriverNoPrompt, True, "ODBC;DATABASE=MyDatabase;UID=username;PWD=password;DSN=MySystemDSN")

    'Create SQL query string (p_MyStoredProc takes two params and then SELECTs from the db table)
    ReportQuery = "{call MyDB.dbo.p_MyStoredProc (?,?)}"
    
    'Call the SQL query to initialse the RecordSet
    Set qry = con.CreateQueryDef("RunStoredProc", ReportQuery)
    qry.Parameters(0).Direction = dbParamInput
    qry.Parameters(0).Type = dbText
    qry.Parameters(0) = MyParamString
    qry.Parameters(1).Direction = dbParamInput
    qry.Parameters(1).Type = dbInteger
    qry.Parameters(1) = MyParamInteger
    Set rst = qry.OpenRecordset(dbOpenSnapshot)

    'Print the entire RecordSet onto a worksheet
    Worksheets("ReportDetails").Range("A1").CopyFromRecordset rst, rst.RecordCount, rst.Fields.Count
 

Richard Schollar

MrExcel MVP
Joined
Apr 19, 2005
Messages
23,707
Hmm

That's a shame about the DAO library being 3.6 - had it been 3.5 say, I'd have said you might have got the output working by going up to 3.6 (I've come across a similar-ish problem here at my work where queries return Chinese characters if using DAo3.51 and below - something to do with Unicode). Potentially, you could try changing the reference to DAO3.5 or 3.51 (and unselect 3.6), but I don't have a lot of confidence that will work.

I must admit, I've never connected to an SQLServer Db using this kind of scripting - you're effectively using an intermediate step of dragging into Access and then out again (at least I think this is what it's doing - could be wrong though).

Richard
 

Forum statistics

Threads
1,136,420
Messages
5,675,751
Members
419,585
Latest member
popsin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top