Hi all , im currently trying to connect a SQL server using the Microsoft OLE DB Provider for SQL Server (Data Environment) in VB
The error that came out while refreshing server is = "Error Enumerating data servers, Enumerator report Unspecified error"
Im a pure Sql noob coz before this im using MS Access so i would like to know how do i actually connect a sql server in VB's data environment?
Am i to understand that, the Sql server is used to hold multiple databases so everytime we need to view the data in another machine...we had to export/import those databases to Sql Server?
Unless you are using a scripting language, never use CreateObject, as it will be very slow compared to Swinger's syntax (I'll leave out the IDispatch vs v-Table binding speech, but trust me, it's faster the way Swinger is saying).
The use of DSN's can be easier. It will also not be as fast as using an OLEDB connection string, as you will have to go VB->ODBC->OLEDB->SQL and back. A connection string will let you skip the ODBC layer. Most likely, you will not notice the performance hit unless you are really hammering the app.
FYI: a connection string might look like:
SET objConn=New ADODB.Connection
objConn.Open "Provider=SQLOLEDB.1;DataSource=MyServer;Initi al Catalog=MyDatabase","myUsername","myPassword"
Oh, and before you use this type of syntax, you will need to create a reference to the Microsoft ADO Library in Tool/References.
hi, thx for all who replied
well i once used OLEDB before, its pretty time consuming (im kinda running outta time :P) , that is until i found out how use Data Environment, which is way faster than OLEDB
well guess i'll have to adapt the OLEDB style then
another question i need to ask
lets say i've managed to connect to the SQL server with OLEDB
now i need to run a test, running the program in different pc (in a LAN network) to store data in the SQL server
how do i do this? can i just pass the data to the server (example Con1.addnew) or do i need to learn socket programming to handle this?
can the Sql server be automaticly recognized within the network?
As long as the SQL Server in question is on the LAN you should be able to access it (assuming you have the proper permissions). You should PING the machine to make sure it is available on the network.
Another note on Connection Strings:
The syntax for connections strings can get a little bit nasty. A simple way to get your connection string is to create a text file (test.txt) on the desktop or some other directory and change the extension to .udl (test.udl). Once done the icon will change to that of a data link. Double click on the icon and enter the necessary data on the tabbed form. Once done change the extension back to .txt, open the file and copy and the connection string into your vb project. This method works for both DSN as well and lets you test the connection.
...and no, you don't need to know anything about socket programming.
cpn: great tip on the .udl file trick. I used to use the ADOControl trick, and have forced myself to learn the connection string syntax, because I hated constantly opening a VB project just to create a connection string -- tough when you connect to a bunch of different data sources (SQL, Informix, flat-files, etc). The best thing is I can build and keep a single text file with all the strings I will need.
Well, I suppose I could have done that anyway... hmmm.