My client has several Access databased that they wanted converted into MSSQL to be able to access them online (excuse the pun). I added the first Access database, LenderPrograms, as a table in the MSSQL database with no problem, it runs fine. But when I tried to add the other Access database, LenderContacts, as another table, it doesn't work.
When I first import LenderContacts using Enterprise Manager it turns all of the "text" fields into "nvarchar" and the database won't return anything at all even though there is data in the table and it connected to the database. If I turn all of the "nvarchar" fields into "varchar" then it returns as many rows as I tell it to, but everything is blank. In other words, if I tell it to return everything in the table (60 records), it shows me 60 blank rows. If I tell it to return a specific ID, it gives me one blank row.
As far as I can tell, it's not a problem with the code. I copied the code directly from the other page that works with LenderPrograms, and as soon as I try to bring up data from LenderContacts, it gives me blank spaces where data should be. I even tried exporting it from Access into different database formats then importing them that way into MSSQL incase there was a problem going directly from Access to MSSQL... no luck.
I'm completely at a loss as to what's going on here. It could be the Access database, the MSSQL database, my code or any combination thereof!