insert query from remote to local using classic vb

Your code use an Access database, but you are asking about how to conect to a SQL server.
May be you want just to export you want your Access database to SQL server, am I right?

Is con pointing to access or sql?

Jajajajajajaja!!!!!!!!

@ LIONKING
con is pointing to access
conSrv is for SQL Server


@TI2Heaven
i am not asking how to connect sql server..?
and i dont want to export access db to sql server.?
Please read my question agian.
< i can retrieving records from sql server and displaying to msgrid.>

Do you have access to the SQL Server (the one you say is local)?
If you do, you could create a linked server to the remote SQL Server and query using the 4 part syntax.

Something like:

SELECT * FROM LinkedServer.Database.Schema.Table WHERE myCondition

Would that work for you?

Remote SQL Server
and local is MS Access 2003

Why don't you ask Access to link to a SQL table so you can treat sql tables as they where in access?

@T12Heaven
<Why don't you ask Access to link to a SQL table so you can treat sql tables as they where in access?>

Please show me how to link to a Remote SQL Table

Link SQL Server tables from an Access database (.mdb)
Start Here. If you cant continue continue asking

I found a more clear link

Thanks T12Heaven
already i did that from this link
http://www.mssqltips.com/sqlservertip/1480/configure-microsoft-access-linked-tables-with-a-sql-server-database/

now i got SQL table into my access database.
and i can access this table direct from MS Access Database

But the problem is using my application i can not retrieve records from this table.
Getting Runtime Error --  ODBC Connection to 'MYServer' failed.
Highlighted on following line

  Set rs = Con.Execute("SELECT * FROM [ODBC;DSN=MyServer;uid=myname;pwd=mypass;database=D:\MyTestDB].dbo_MyTablel_Cust")

Please, before you try to connect with Visual Basic code to your Access database you need to stay in Access. Check that your link works will by designing a silly view that use the table you just linked.
If you can work with the table in Access forget about the code just send me:
Set rs = Con.Execute("SELECT * FROM [ODBC;DSN=MyServer;uid=myname;pwd=mypass;database=D:\MyTestDB].dbo_MyTablel_Cust")
As I mention you can continue using your original one:
con.Execute ("INSERT INTO MyTable select * from [;DATABASE=" & App.Path & "\MyDatabase.mdb;PWD=abcdef]. dbo_MyTablel_Cust where MyField = " & MshBNo & "")
You have not mentioned your network environment. Are you in a Domain?, Have you use user and password of your domain?.

Ok it was DSN problem , i deleted previous one and re-create new one, link is working.
from access i can open SQL Table.
One more thing,  from vb i can retrieve all records from SQL Table.

Now i am trying to insert But getting Runtime error

Field 'MyLocalTable.Field3' can not be a zero-length string


con.Execute ("INSERT INTO MyLocalTable SELECT [Field1],[Field2],[Field3],[Field4] FROM [;DATABASE=D:\MySQLTable.mdb].dbo_SqlTable_Cust WHERE Field1 = " & MshBNo & "")

yes you are right .. there is couples of fields like that..

let me fix ...

Thanks guys