Search results

When I relink the tables Connect column of the 'msysobjects' system table still shows blank on the row that has been generated.

I just can't understand. I have created 2 System Data Sources. dbTest and dbLive. One should be referencing to my local SQL instance and dbLive to remote live SQL instance. What I found is that when I change connection through ODBC Data Source Administrator to dbTest, it doesn't points to...

I see, I was confused because the next few columns are create_date and modified_date and the dates corresponding to the Column field with values were not recent. Does this means that every time I open my Access Column field displays information for the current connection? I suppose I was...

Thank you Bob, I did that, but out of say 700 rows only 10 will have a result for Connect field. The rest are Nulls. And those 10 are dated few months back.

Hi, I am trying to connect via ADO to either local SQL instance or remote depending on what ODBC connection my Access is connected to. So for example if my Access application is connected to local instance through ODBC driver, then I would make ADO connection to a Local instance. If it's...

Hi Leigh, Thanks for your reply. About rst1.Open sqlVersion1, cn2 you should use cn2.Execute sqlVersion1 I knew that was not quite right. But I have been using on my earlier codes when i was just writing records to Excel worksheets and it was returning records. I will change that anyway...

Hi guys, I am still having trouble with it. I have tried several ways, but no luck. I am posting my code here in case anyone can help, thanksSub MyTestModule() Dim cn As New ADODB.Connection Dim cn2 As New ADODB.Connection Dim rst1 As ADODB.Recordset Dim rst2 As...

I have managed to do it. What I am really looking is to stop using Recordsets and try to use in INSERT query such as: INSERT INTO table1(Field1, Field2) SELECT field1, field2 FROM [" & connectionToSQLServerVar & "].table2 I can use INSERT query from one table to another within same Access...

I am still getting "Syntax error in FROM clause" Any ideas please?

Thanks, I think i have an idea now. For some reason when I am using this for SQL Server works fine: Set rst1 = New ADODB.Recordset rst1.CursorLocation = adUseClient rst1.Open strSQL1, cn, adOpenKeyset, adLockPessimistic But when I use this for Access - it dowsn't work. I have new...

Thanks Ken, What i was doing is this: INSERT INTO [myAccessTable] (col1, col2) FROM rst1 And obviously it doesn't work because I can't pass rst1 (which is a variable for my SQL Recordset result) to INSERT query. I have a Recordset with my Access table as well, but would you be more...

Hi, I have a SQL query written in Excel VB Module, which generates the data and stores in Recordset. Like this: Set rst = New ADODB.Recordset rst.CursorLocation = adUseClient rst.Open sqlString, connectionVar, adOpenKeyset, adLockPessimistic With this I can copy the data into Excel...

But why then I can't connect via ADODB.Connection, but can connect through Excel when I record Macro and Link tables from the SQL server manually?

Thanks for a reply, I might not make sense sometimes as I have very limited knowledge in SQL servers. I think the maximum limit (3 in this case) is for connecting to a terminal server where Microsoft SQL Server resides and not to SQL Server itself. So I think I only need ID and Password...

I am importing tables manually in Access by going to File->Get External Data->Link Tables This opens 'Link' window, then under 'Files of Type' selecting 'ODBC Databases()' This opens 'Select Data Source' window, and under 'Machine Data Source' tab selecting 'myDSN' Data Source Name of type...

OK, I have an Access .mdb file. In it I have linked tables from a sql server, by going to File->Get External Data etc... Then I have created some queries based on these linked tables. When I open and run manually queries - they run fine - no problem. Then I have created modules as I need to...

I probably need to mention that I do not need to be connected remotely to a server. I have already linked the tables to access file. Also, we have different loginID and password to connect directly to the server and is limited to maximum 3 concurent users. But when I link tables from...

Thanks again for your reply, I have changed to: Set cn = CurrentProject.Connection and it works fine. Then I have included this code: cn.Open "Provider=SQLOLEDB;Data Source=servername;User ID=myID;Password=myPassword;Initial Catalog=databaseName I was getting run time errors...

Thanks LPurvis, I wrote my code in Visual Basic editor from Microsoft Access. The tables are linked (and hence i get the error), but when are imported it works fine. The linked tables operate fine when opened from the Access window. I do not plan to do any heterogenous join, but the reason I...

Hi, I wrote a module which connects to Jet database (Access) through ADO and runs a query. The code runs only when i import those tables from a SQL server. When I try to link those tables (so that every time code runs a query generates updated results) I get "ODBC Connection to Server...