I have an existing application with frontend in VB 6.0 and backend in MS Access. I am trying to convert the application to SQL Server 2000 (standard) backend. At the moment the frontend uses ODBCDirect to communicate with MS Access backend. I upsized the MS Access database to SQL Server 2000 using MS Access upsizing wizard. (I know you need to use DTS, but I tried that and it did not copy the data across citing the error "The Server could not load DCOM", hence I used the upsizing wizard).
The backend now set up in SQL Server, I presume all I need to do is change the connection string in VB6.0, which I did, but I have not been able to make a successful connection yet. The following is the connection string I am using:
The SQL Server has been configured for windows authentication and the new database that I created due to upsizing, in the User section, it lists "dbo" as user name and login name is "domain/myloginname". So in the user ID I am using "dbo". I got some hints about how to connecte via ODBCDirect to SQL Server using the required workspace, as follows:
Set dbw = CreateWorkspace("pomsws", "admin", "", dbUseODBC)
Set pomsc = dbw.OpenConnection("pompom", dbDriverNoPrompt, _
where con_string is the above mentioned connection string.
Can anybody let me know what I am doing wrong and provide any suggestions?
An update on my question. I created a seperate small application just to test for the connectivity. The following is the code:
Dim strConnectionAdmin As String
Dim objConnAdmin As Connection
Dim CollegeRs As Recordset
Dim strsql As String
Dim i As Integer
Dim mdbodbcdirect As Database
Dim dbw As Workspace
Dim pomsc As Connection